nesting functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i need to nest vlookup functions to search one worksheet
and if item is not there go to another worksheet to find
item.
 
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
 
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.
 
You might want to change iserror to isna . iserror is more generic whereas
isna is more specific to being unable to locate the match.
 
You might wish to replace iserror to isna as isna is more specific to
missing data. But otherwise, it should be okay.
 
Deja Vu all over again... :-)


J.E. McGimpsey said:
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.
Click to expand...
 
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))
 
Back
Top