Help! I need to impress my future boss with something in Excel and I have 2 hours to do it!

[MrsThierryHenry]

I'm trying to work out how to create an invoice as part of a recruitment process. I need to create a formula to do the following:

In column B I enter the product code, and then in column C the product description should automatically appear. I have successfully done this using one product description:

=IF(B14="H12co","Hearts 12 pack Coast")

However there are about 100 different products, so I need to create a formula which contains any one of these 100 product descriptions. Help!

YOu need to use a vlookup table. You create the table in one sheet, listing code in one column and description in other. Then you use formula vlookup,table and it brings in result. Look in excel help fro vlookup - it's quite ridiculously easy

Hey Prufrock, thanks! Just before you posted I was trying a LOOKUP formula which I found on the web, but couldn't be sure that I had the right idea. I'll take a look in the help section - I feel like I'm halfway there now!

Okay, I've tried this with VLOOKUP but it brings up a 'REF!' error message.

Using LOOKUP, it's almost working:

=LOOKUP(B14,G1:G13,H1:H13)

Problem is that it always returns the last item on the list, i.e. square H13. Any ideas where I'm going wrong?

So, what you want is to create a product list

Column A - item numbers
Column B - product description

Then decide which cell you will put your item code in.

In the cell that you want to see the description displayed, type the formula:

=VLOOKUP(A1,A1:B8,2,FALSE)

Where A9 is the cell that you will type the item code for the invoice, A1:B8 is the range containing the list of item codes and product descriptions, and 2 is the column that contains the product description.

Bless you, lou! With just 10 minutes to go I did it myself - using the same formula as you! Thanks loads anyway! xxx

Well done - knock him out with your new and superior skills!

Can I just recommend - if you have any future Excel issues - mrexcel.com is a fantastic forum for getting answers to this type of question. And for generally learning more about Excel - pretty much all of the advanced stuff I know, I learnt there.

Thanks so much for your help! (It's a 'she' actually!). Cornflakegirl - thanks also for that useful link, I'll make a note of it.

xx