nesting functions

[Guest]

i need to nest vlookup functions to search one worksheet
and if item is not there go to another worksheet to find
item.

 

[Kevin Stecyk]

Anon,

=if(iserror(vlookup(FirstSheetParameters), vlookup(SecondSheetParameters),
vlookup(FirstSheetParameters))

If there is an error, go to the second sheet and search, otherwise, go to
first sheet and search.

In the future, you might want to provide more details so that the person
responding can be more precise in their answer.

Regards,
Kevin

 

[J.E. McGimpsey]

one way:

=IF(ISNA(MATCH(Lvalue, OFFSET(table1,,,,1), FALSE)),
IF(ISNA(MATCH(Lvalue, OFFSET(table2,,,,1), FALSE)), "",
VLOOKUP(Lvalue, table2, 2, FALSE)), VLOOKUP(Lvalue, table1, 2,
FALSE))

where table1 and table2 are references to your lookup tables.

 

[Kevin Stecyk]

You might want to change iserror to isna . iserror is more generic whereas
isna is more specific to being unable to locate the match.

 

[Kevin Stecyk]

You might wish to replace iserror to isna as isna is more specific to
missing data. But otherwise, it should be okay.

 

[Kevin Stecyk]

Deja Vu all over again...

one way:

=IF(ISNA(MATCH(Lvalue, OFFSET(table1,,,,1), FALSE)),
IF(ISNA(MATCH(Lvalue, OFFSET(table2,,,,1), FALSE)), "",
VLOOKUP(Lvalue, table2, 2, FALSE)), VLOOKUP(Lvalue, table1, 2,
FALSE))

where table1 and table2 are references to your lookup tables.

 

[Arvi Laanemets]

Hi

With return values as strings
=IF(ISERROR(VLOOKUP(Value,FirstRange,n,FALSE)),"",VLOOKUP(Value,FirstRange,n
,FALSE)) &
IF(ISERROR(VLOOKUP(Value,SecondRange,n,FALSE)),"",VLOOKUP(Value,SecondRange,
n,FALSE))

With return values as numbers/dates/times
=IF(ISERROR(VLOOKUP(Value,FirstRange,n,FALSE)),0,VLOOKUP(Value,FirstRange,n,
FALSE)) +
IF(ISERROR(VLOOKUP(Value,SecondRange,n,FALSE)),0,VLOOKUP(Value,SecondRange,n
,FALSE))