VLookup against one of two columns

[Mark]

I have a spreadsheet which allows a sales rep to enter a part number and
have the price column populated - normal vlookup works fine for this.

However, the lookup table contains 2 part numbers (ours and a competitors)
and I want them to be able to enter either of those codes and have the price
populated

Col A Col B
Enter code: Price
123 OR xyz £1.99

Lookup table is:

A B C
Code Code2 Price

I'm not sure whether i should be using Match, Index or VLookup for this, so
any help much appreciated.

 

[bpeltzer]

I'd stick with vlookup, but allow for the possibility that the lookup into
the first column may fail and should then trigger a lookup into the second
column:
=if(isna(vlookup(a2,Sheet2!a:c,3,false)),vlookup(a2,Sheet2!b:c,2,false),vlookup(a2,Sheet2!a:c,3,false))
(You could replace the first vlookup w/ match(a2,Sheet2!a:a,false), but it's
six of one...)
Also realize that if the product numbers between you and the competitor ever
overlap, you've got trouble.
--Bruce