How to fix #N/A if vlookup can't find a match to the lookup value.

For example,

Array = D1:E2
D1:E2 =
Apple 1
Orange 2

Lookup value = A1
A1 = Pear

vlookup(A1, D1:E2, 2,0)

Result will return as #N/A since "Pear" cannot be found in the array.

Question: how do I set it to blank or 0 if it's #N/A?

Please help! Thank you.

Try =IF(ISNA(vlookup(A1, D1:E2, 2,0)),"not found", vlookup(A1, D1:E2, 2,0))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Sweepea" wrote in message
...
How to fix #N/A if vlookup can't find a match to the lookup value.

For example,

Array = D1:E2
D1:E2 =
Apple 1
Orange 2

Lookup value = A1
A1 = Pear

vlookup(A1, D1:E2, 2,0)

Result will return as #N/A since "Pear" cannot be found in the array.

Question: how do I set it to blank or 0 if it's #N/A?

Please help! Thank you.

Thanks so much, Bernard. It works!!!!


"Bernard Liengme" wrote:

Try =IF(ISNA(vlookup(A1, D1:E2, 2,0)),"not found", vlookup(A1, D1:E2, 2,0))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Sweepea" wrote in message
...
How to fix #N/A if vlookup can't find a match to the lookup value.

For example,

Array = D1:E2
D1:E2 =
Apple 1
Orange 2

Lookup value = A1
A1 = Pear

vlookup(A1, D1:E2, 2,0)

Result will return as #N/A since "Pear" cannot be found in the array.

Question: how do I set it to blank or 0 if it's #N/A?

Please help! Thank you.