1. Multiple Vlookup (Excel 2002)

Hi

Is it possible to do a multiiple vlookup for example.

I can't combine the tables because the item code in A2 may appear in both tables, so the first lookup must be in table 1, but if it can' t be found in table1 then I will accept it from table 2.

2. Re: Multiple Vlookup (Excel 2002)

=IF(ISERROR(VLOOKUP(A2,Table1,3,FALSE)),VLOOKUP(A2 ,Table2,3,FALSE),VLOOKUP(A2,Table1,3,FALSE))

It looks in table1, if not found it generates an error, so it looks in Table2,
If no error is generated by looking in Table1, it gets the value.

If it is not found in either, it generates the #N/A error

Steve

3. Re: Multiple Vlookup (Excel 2002)

Hi Steve

Thanks very much , I am most grateful.

4. Re: Multiple Vlookup (Excel 2002)

A 2-cell approach...

X2 (or any other ceonvenient cell)...

=IF(ISNA(Y2),VLOOKUP(A2,TABLE2,3,0),X2)

Y2:

=VLOOKUP(A2,TABLE1,3,0)

0 stands for FALSE, which lookup functions like VLOOKUP, HLOOKUP, and MATCH also accept as the match-type (range_lookup in MS parlance) specification.

Note that X2 is the result cell.

5. Re: Multiple Vlookup (Excel 2002)

This is an interesting approach , I never realised 0 = false, saves some typying does this mean 1= true?.

Yes.

7. Re: Multiple Vlookup (Excel 2002)

More strictly speaking, the way excel works, 0 is false, all other numbers will act as true if used in a formula.

If you have something that results in TRUE and you convert it to a number (eg by multiplying it by 1), it results in a 1. But any number (other than 0) is considered "True".

Note in VB (as opposed to excel) a True has a value of -1 not +1.

Steve

8. Re: Multiple Vlookup (Excel 2002)

<hr>Note in VB (as opposed to excel) a True has a value of -1 not +1.<hr>Handy that, isn't it! <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15>

Alan

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•