Vlookup Help - multiple column id's

[mpalmer]

i am using vlookup in an invoice situation

i have my products columns set up as - sku / description / price

i use the sku to pull over the description and price to the invoice

i would like to add another column which would be an alternate sku number
and then on the invoice - we could enter either the sku number or alt sku number and have them bring over the info

here is the formula i am using now with just the one sku column

=IF(ISBLANK(B11),"",VLOOKUP(B11,Products,3,FALSE))

Thanks fr any help and or suggestions

[davesexcel]

Change the 3 to a 4 in your formula?

[NBVC]

Possibly something like:

=IF(ISBLANK(B11),"",IF(ISNA(VLOOKUP(B11,Products,3,FALSE)),VLOOKUP(B11,Products2,2,FALSE),VLOOKUP(B11,Products,3,FALSE)))

where Products2 is the name of the alternate table starting with the alternate sku in left most column.

[mpalmer]

Thanks for the quick replies - but i am getting an error - so bear with me

my products range now consists of

sku / alt sku / description / price

what would this do to the formula?

[NBVC]

Say that table is in d2:G100, then

=IF(ISBLANK(B11),"",IF(ISNA(VLOOKUP(B11,$D$2:$G$100,4,FALSE)),VLOOKUP(B11,$E$2:$G$100,3,FALSE),VLOOKUP(B11,$D$2:$G$100,4,FALSE)))


So if it doesn't find it by looking up the B11 sku in D2:D100, the it looks in E2:E100, and it will pull the Price from appropriate column.

Adjust ranges to suit or to match your named ranges.

[mpalmer]

Got it - works perfectly - Thanks!