vlookup from two sources - syntax of vlookup statement

  • Thread starter Thread starter Roger on Excel
  • Start date Start date
R

Roger on Excel

[Excel 2003]

Is it possible to use syntax to perform a vlookup from a source and then
another if the first is False?

For example,

My primary table of data is called "Materials" and my second source is a
range A81:E140 on the same sheet as the vlookup,

So something like :

=vlookup(A1,Materials,2,vlookup(A1,A81:E140),2,False)

Can anyone help with advice on syntax?

Thankyou,

Roger
 
the false in a Vlookup is the mtach type, not an value to use if the formula
is false.

=if(iserror(vlookup(A1,Materials,2,false)),Vlookup(A1,A81:E140,2,false),Vlookup(A1,Materials,2,false)) would work.
If the Vlookup(A1,Materials,2,false) is an error (not found) then the second
condition Vlookup would be used.

If you are using Excel 2007 you can use the new IFERROR function to simplify
even further

=IFERROR(VLOOKUP(A1,Materials,2,FALSE),VLOOKUP(A1,A81:E140,2,FALSE))

With this formula if the first function evaluates to a error the second
function is used.
 
This should work but I have not tested it

=if(isna(vlookup(A1,Materials,2,False)),vlookup(A1,A81:E140,2,False),vlookup(A1,Materials,2,False))

This say if the Materials lookup fails, then use the same-page lookup, other
why use the Material lookup

If you have Excel 2007 (or beta 2010) then a simpler formula works:

=iferror(vlookup(A1,Materials,2,False),vlookup(A1,A81:E140,2,False)

best wishes
 
Back
Top