vlookup from two sources - syntax of vlookup statement

[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

 

[Paul C]

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.

 

[Bernard Liengme]

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