Vlookup 2 tables (excel 2002/03)

Topic

New Reply

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,Colist,4,0))

Thanks

Braddy

Reply | Quote

Replies

Try

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

Reply | Quote

HI Hans

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

Thanks again

Braddy

Reply | Quote

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

Braddy

Reply | Quote

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

Try:

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

Reply | Quote

HI Legare

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

Braddy

Reply | Quote

(Edited by sdckapr on 30-Mar-05 10:04. Corrected my mistake (Thanks Rudi for pointing it out))

The limit is 7 nested functions.

Steve

Reply | Quote

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

Reply | Quote

I was thinking you could have 7 iFs, but that you are correct, 7 nested…

Steve

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

If the retrieval result is a number…

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

The idiom is due to fairwinds: http://tinyurl.com/6db2f%5B/url%5D

Reply | Quote

Hi Aladin

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

Braddy

Reply | Quote

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.

Reply | Quote

HI Aladin

Thanks for explanation.

Braddy

Reply | Quote