vlookup
 

I need a way of testing the return value from a vlookup call.
It will happen that the lookup will fail and the return will be #N/A, which
is not nicest thing to see on a spreadsheet! I have tried "if" statement
variations but testing the cell is unsuccessful as the 'value'of the cell is
not what is displayed!

Jim Crawford
Alden Press
Oxford UK

To return an empty cell if a match is not found:

=IF(ISNA(MATCH(D1,A1:A100,0)),"",VLOOKUP(D1,A1:C10 0,3,0))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Jimat56" wrote in message
...
I need a way of testing the return value from a vlookup call.
It will happen that the lookup will fail and the return will be #N/A,
which
is not nicest thing to see on a spreadsheet! I have tried "if" statement
variations but testing the cell is unsuccessful as the 'value'of the cell
is
not what is displayed!

Jim Crawford
Alden Press
Oxford UK

Assume you VLOOKUP would look like this

=VLOOKUP(A2,B2:D400,2,FALSE)

=IF(ISNUMBER(MATCH(A2,B2:B400,0)),VLOOKUP(A2,B2:D4 00,2,FALSE),"")

that is the best way to error check it, will return a blank cell if value
not found


Regards,

Peo Sjoblom

"Jimat56" wrote:

I need a way of testing the return value from a vlookup call.
It will happen that the lookup will fail and the return will be #N/A, which
is not nicest thing to see on a spreadsheet! I have tried "if" statement
variations but testing the cell is unsuccessful as the 'value'of the cell is
not what is displayed!

Jim Crawford
Alden Press
Oxford UK