INDEX/MATCH with no match

[Georgia Golfer]

I really appreciate all the help that I've received here.

Using that help, I built my own index/match function. It works, unless there is no match, then I get the #NA result. I want it to return a blank.

I tried the ISERROR and that converts it to true / false & it won't let me add "" to the end, like a general 'if' statement.

Thanks

[martindwilson]

you have to wrap it in IF
if(iserror(your index match here),"",your index match here)

e.g

if(iserror(INDEX(G1:H8,MATCH(A1,G1:G11,0),2)),"",INDEX(G1:H8,MATCH(A1,G1:G11,0),2))

[JBeaucaire]

If you're going to have a LOT of IF(ISERROR() formulas, you might want to put the IFERROR function from Excel 2007 into your version of Excel. If you don't, you're effectively making Excel do every calculation twice.

The IFERROR User-Defined Function stops that, and the formula gets simpler, too.

=IFERROR(youroriginalformulahere, "")

Please Login or Register  to view this content.

Copy that code into a Module (same place your regular macros go) and you can use it straightaway.