How to return blank result if no match for VLOOKUP?

[tkroper]

I'm using this formula (thanks to dafan & HalfAce http://www.mrexcel.com/forum/showthread.php?p=1604964&posted=1#post1604964)

=IF(VLOOKUP($A177,$A$736:$C$787,3,FALSE)=0,"",VLOOKUP($A177,$A$736:$I$787,3,FALSE))

to return the value located in the 3rd column of the range when the value in the 1st column of the range matches the data in A177.

However, there isn't a match for the data in A178 in the range, so the formula returns "#N/A". I would like the cell containing the formula to remain blank in those instances. Is there a way to do this?

Thank you very much for your help!

Todd

 

[dafan]

=IF(OR(VLOOKUP($A177,$A$736:$C$787,3,FALSE)=0,ISNA(VLOOKUP($A177,$A$736:$C$787,3,FALSE)=0)),"",VLOOKUP($A177,$A$736:$I$787,3,FALSE))

You can test with IF(ISNA( < value > ) if a formula generates a #N/A.

 

[tkroper]

Thank you dafan! I still get a #N/A result though.

I noticed I should have shown $C$787 instead of $I$787 and changed it, but that didn't make a difference.

If I follow, your "IF(ISNA" should make the formula think it's found a match. I wonder then if it's attempting to return the value in the 3rd column of the table but can't locate one.

 

[dafan]

Sorry typo I think:

=IF(OR(VLOOKUP($A177,$A$736:$I$787,3,FALSE)=0,ISNA(VLOOKUP($A177,$A$736:$I$787,3,FALSE))),"",VLOOKUP($A177,$A$736:$I$787,3,FALSE))

</pre>

 

[tkroper]

I appreciate all your help dafan - thank you very much!

 

[njimack]

A shorter method would be:
=IF(COUNTIF($A$736:$A$787),VLOOKUP($A177,$A$736:$C$787,3,FALSE),"")

 

[tkroper]

A shorter method would be:
=IF(COUNTIF($A$736:$A$787),VLOOKUP($A177,$A$736:$C$787,3,FALSE),"")

Thank you njimack! I tried your solution, but got an error that there are too few arguments for this formula. I tried fiddling around with it but I'm not knowledgeable enough with formulas to correct it.

 

[spcalan]

What about adding an If(iserrr( to the beginning?

 

[tkroper]

I couldn't stop fiddling, and came up with this:

=IF(ISNA(VLOOKUP($A178,$A$736:$C$787,3,FALSE)),"",VLOOKUP($A178,$A$736:$C$787,3,FALSE))

It seems to do the trick. Thank you very much for helping me through this one njimack!

 

[njimack]

That's still not very efficient, since you're performing the VLOOKUP twice. There was a missing argument in my previous post. Try the following:

=IF(COUNTIF($A$736:$A$787,$A177),VLOOKUP($A177,$A$736:$C$787,3,FALSE),"")

I couldn't stop fiddling, and came up with this:

=IF(ISNA(VLOOKUP($A178,$A$736:$C$787,3,FALSE)),"",VLOOKUP($A178,$A$736:$C$787,3,FALSE))

It seems to do the trick. Thank you very much for helping me through this one njimack!