VLookup against one of two columns

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.

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

"Mark" wrote:

>
> 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.
>

On Mon, 13 Mar 2006 08:10:33 -0800, Mark
<[email protected]> wrote:

>
>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.

If none of your part numbers are the same as your competitor's part,
why not just list all the part numbers, yours and your competitors in
column A, with the price in col. B. (Perhaps adding a note in column C
that says "Ours", or "Comp" in case you ever need to use it in future
calcs).


HTH




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________