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

 

[tkroper]

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),"")

Yes - I see what you mean, Neil - and it works perfectly! Thank you very much

 

[tkroper]

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

Thank you spcalan - in my formula, using "isna" accomplished what I needed, but Neil's solution was much better and more efficient. I do appreciate your help though - thank you very much!

 

[Aladin Akyurek]

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),"")

Neither is CountIf: See

http://www.mrexcel.com/forum/showthread.php?t=38643


Options (where match-type = 0) from highly efficient to less efficient...

[1]

=IFERROR(INDEX($C$736:$C$787,MATCH($A177,$A$736:$A$787,0)),"")

Required: Excel 2007 or beyond.

[2]

=IF(ISNUMBER(SETV(MATCH($A177,$A$736:$A$787,0))),
    INDEX($C$736:$C$787,GETV()),"")

Required: morefunc.xll add-in

[3]

=IF(LOOKUP($A177,$A$736:$A$787)=$A177,
    LOOKUP($A177,$A$736:$A$787,$C$736:$C$787),
    "")

Required: $A$736:$C$787 must be sorted in ascending order on column A.

[4]

Y177:

=MATCH($A177,$A$736:$A$787,0)

X177:

=IF(ISNUMBER($Y177),INDEX($C$736:$C$787,$Y177),"")

Required: Additional formula cells.

[5]

=IF(ISNUMBER(MATCH($A177,$A$736:$A$787,0)),
    INDEX($C$736:$C$787,MATCH($A177,$A$736:$A$787,0)),
    "")

[6]

VLOOKUP versions of [1], [2], [4], and [5].

[7]

=IF(COUNTIF($A$736:$A$787,$A177),
    INDEX($C$736:$C$787,MATCH($A177,$A$736:$A$787,0)),
    "")

Compare the above list with one posted much earlier:

http://www.mrexcel.com/forum/showthread.php?t=59718

 

[tkroper]

Wow Aladin! That's a lot of good information - thank you very much. I'm going to have fun exploring all of your suggestions.