# Thread: Vlookup 2 tables (excel 2002/03)

1. ## Vlookup 2 tables (excel 2002/03)

Hi

I am trying to do the following I want to look up A6 from a table called FoodPrice, if it can't find it there look in a table called colist I would be grateful for any help.

=IF(ISNA(VLOOKUP(A6,FoodPrice,4,0),VLOOKUP(A6,Coli st,4,0))

Thanks

2. ## Re: Vlookup 2 tables (excel 2002/03)

Try

=IF(ISNA(VLOOKUP(A6,FoodPrice,4,FALSE)),VLOOKUP(A6 ,Colist,4,FALSE),VLOOKUP(A6,FoodPrice,4,FALSE))

3. ## Re: Vlookup 2 tables (excel 2002/03)

HI Hans

Thanks for your prompt reply I will apply it and let you know how I get on.

Thanks again

4. ## Re: Vlookup 2 tables (excel 2002/03)

Hi Hans

Thank you very much your formula worked fine, could I impose on you to show me if a further table could be added say Agriculture for instance. Or is there a limit?

Many Thanks

5. ## Re: Vlookup 2 tables (excel 2002/03)

Edited by HansV to break very long line that caused horizontal scrolling. The formula should be entered as one line, though.

Try:

<pre>=IF(ISNA(VLOOKUP(A6,FoodPrice,4,FALSE)),if(is na(VLOOKUP(A6,Colist,4,FALSE)),
VLOOKUP(A6,Agriculture,4,FALSE),VLOOKUP(A6,Colist, 4,FALSE)),VLOOKUP(A6,FoodPrice,4,FALSE))
</pre>

6. ## Re: Vlookup 2 tables (excel 2002/03)

HI Legare

Thanks very much for the reply and thanks to all who responded.

7. ## Re: Vlookup 2 tables (excel 2002/03)

The limit is 7 nested functions AFAIK!
(Its one better than yours Steve!)

8. ## Re: Vlookup 2 tables (excel 2002/03)

<img src=/S/blush.gif border=0 alt=blush width=15 height=15> I was thinking you could have 7 iFs, but that you are correct, 7 nested...

Steve

9. ## Re: Vlookup 2 tables (excel 2002/03)

<P ID="edit" class=small>(Edited by sdckapr on 30-Mar-05 10:04. Corrected my mistake (Thanks Rudi for pointing it out))</P>The limit is 7 nested functions.

Steve

10. ## Re: Vlookup 2 tables (excel 2002/03)

(Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

If the retrieval result is a number...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},VLOOK UP(A6,FoodPrice,4,0),VLOOKUP(A6,Colist,4,0),VLOOKU P(A6,Agriculture,4,0)))

The idiom is due to fairwinds: http://tinyurl.com/6db2f

11. ## Re: Vlookup 2 tables (excel 2002/03)

Thanks for your reply, it looks very interesting. I would like to understand the first part up to choose, I will give it a try and let you know.

Thanks

12. ## Re: Vlookup 2 tables (excel 2002/03)

LOOKUP(9.99999999999999E+307,Reference)

returns the last numerical value from Reference.

Reference can be either a range or an array (a vector). F2:F10 is a range, while {#N/A,#N/A,5,2} is an array.

CHOOSE(Idx,Value1,...,ValueN)

where N is between 1 and 29 inclusive, allows to pick out a value from the list of values Value1,...ValueN whose position matches the Idx value. For example:

CHOOSE(2,MAX(A1:B1),MAX(E1:F1)

picks out the value that MAX(E1:F1) returns.

CHOOSE({1,2,3},VLOOKUP(...),VLOOKUP(...),VLOOKUP(. ..))

has an Idx that is a constant array of index numbers: {1,2,3}. This causes to pick out all of the values that 3 VLOOKUP will return. The result will look like, e.g.

{205.80,#N/A,#N/A}

or

{217.65,185.43,#N/A}

or

{#N/A,#N/A,#N/A}

LOOKUP with the big number as lookup value will return 205.80, 185.43, and #N/A, respectively.