If no value found in vLookup, I need to return a NULL to the Cell

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

I'm trying to setup a Lookup Table and if the value is not found, I
need it to return Null. The closest I can seem to get is it is
returning #NA. How do I get around this?

Example: Formula in cell C1 is =vLookup(A1,G:H,2,False)

I've tried incorportating If Conditions, =If(Match;
=if(vLookup ......nothing is working.

Thanks for your help!
 
You can't return a null, buy you can return a null string...

=IF(ISNA(MATCH(A1,G:G,FALSE)),"",VLOOKUP(A1,G:H,2,FALSE))

Or, in XL07

=IFERROR(VLOOKUP(...),"")
 
=IF(ISNA(YourVlookupFormula),"",YourVlookupFormula)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'm trying to setup a Lookup Table and if the value is not found, I
| need it to return Null. The closest I can seem to get is it is
| returning #NA. How do I get around this?
|
| Example: Formula in cell C1 is =vLookup(A1,G:H,2,False)
|
| I've tried incorportating If Conditions, =If(Match;
| =if(vLookup ......nothing is working.
|
| Thanks for your help!
|
 
The easiest way is to use an if function.

ex.

=IF(D5="","",(vlookupfuntion))

I don't know anything about those two functions referenced above, but this one seems a little less bulky and easier to use?

Basically you're saying, IF the cell that you're referencing is blank, return a blank cell, if NOT blank, return the value for the vlookup funtion.
 
Tyler

That's fine if D5 is blank but how about if D5 has a value that cannot be found
in lookup table?

Then why you need the ISNA or ISERROR function.

I prefer ISNA because ISERROR masks all errors, not just the #NA


Gord Dibben MS Excel MVP
 
Back
Top