How to modify this IF formula

[djarcadian]

Here's the working formula.

Please Login or Register  to view this content.

Sometimes the INDEX part of this formula will return a blank. In that event I want to search the AccountList!:K:K for a match instead.

I tried to embed an IF formula like this but I get an error.

Please Login or Register  to view this content.

Any idea what I did wrong?

[Pete_UK]

What error do you get? If it is #N/A then you will need to check to see if A3 is in column K of the AccountList sheet.

You can use IFERROR to do this if your version of Excel is 2007 or later.

Hope this helps.

Pete

[FDibbins]

Also, probably, the error is coming from the MATCH, not the INDEX?
Break the components down to see where the cause is.
Either way, you could probably shorten by using a series of IFERROR's with the MATCH's inside 1 INDEX

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[newdoverman]

I don't get INDEX to return a blank, I get a 0 and this seems to work in my experiments:

Please Login or Register  to view this content.

[Tony Valko]

Sometimes the INDEX part of this formula will return a blank.

I don't get INDEX to return a blank, I get a 0

Might be that the OP's indexed range contains formulas that sometimes return formula blanks.

[newdoverman]

That is entirely possible. I was just working from my own worksheet and didn't take a guess. The OP could substitute for the 0 with ""