How do I create an Excel formula withmultiple options for the results?

[Peddy]

Version: 2008

I need to create an invoice template, where 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!

 

[Bob Greenblatt]

Version: 2008

I need to create an invoice template, where 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!

Somewhere on this or another sheet, build a table of product codes and
descriptions. Then use Vlookup. Check Help for the exact syntax.

 

[Peddy]

Thanks for this post, Bob. I'm nearly there; I've created the following formula:
=VLOOKUP(B16,G1:H13,2)

However, it only selects the last item on the list (i.e. H13). I want it to check the value in B16, match it to the corresponding value in column G and then bring up the 'description' from column H. Does this make sense? Can you see where I'm going wrong?

 

[Peddy]

Okay, I've just done it by adding FALSE at the end of the above formula! One more thing - when I hold and drag the formula down the whole column it alters the lookup value, which is what I want, but it also alters the table array - each time I drag it down a square, the array moves down a square as well. Is there a way to stop this from happening - I want the table array to remain the same.

 

[Bob Greenblatt]

Okay, I've just done it by adding FALSE at the end of the above formula! One
more thing - when I hold and drag the formula down the whole column it alters
the lookup value, which is what I want, but it also alters the table array -
each time I drag it down a square, the array moves down a square as well. Is
there a way to stop this from happening - I want the table array to remain the
same.

Yes, there is a way. Your formula (in the first cell) should look like:
=VLOOKUP(B16,$G$1:$H$13,2,false)

 

[CyberTaz]

It's also a good idea to have the items in the Lookup Table sorted in
ascending order based on the content of the table's first column.