Talk

Advanced search

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!

Prufrock 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?

lou031205 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

lou031205 Thu 10-Jul-08 12:59:39

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

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

Join the discussion

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

Register now