# Talk

Grasp the next rung of the career ladder

Find jobs that fit your kills and your home life with Mumsnet jobs

See all jobs »

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

(9 Posts)
MrsThierryHenry Thu 10-Jul-08 11:14:06

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!

Thu 10-Jul-08 11:17:26

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<<cell that product code is in>>,table and it brings in result. Look in excel help fro vlookup - it's quite ridiculously easy

MrsThierryHenry Thu 10-Jul-08 11:31:54

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!

MrsThierryHenry Thu 10-Jul-08 12:05:04

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?

Thu 10-Jul-08 12:47:33

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.

MrsThierryHenry Thu 10-Jul-08 12:57:49

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

Thu 10-Jul-08 12:59:39

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

cornflakegirl Thu 10-Jul-08 13:29:36

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.

MrsThierryHenry Thu 10-Jul-08 15:29:43

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

Join the discussion

Registering is free, easy, and means you can join in the discussion, get discounts, win prizes and lots more.

Register now