Getting CSE formula to not run if error

[dip11]

Hi,

I'm not very knowledgeable with CSE formulae so I'd like some help verifying if what I'm doing is correct:
{=INDEX(BF:BF,MATCH($ES2&$C2,$AB:$AB&$E:$E,0))}

What clause can I add that if say ES2 or AB are blank, don't run the CSE formula in the first place? I am running it across about 20k rows so being able to reduce the matches it does would help a fair bit.

Or rather, would it even make a difference if I didn't add such a clause (would it evaluate full formula anyway rather than just the error/blank check part?)

[Pete_UK]

You could do it like this:

=IF($ES2&$C2="","",INDEX(BF:BF,MATCH($ES2&$C2,$AB:$AB&$E:$E,0)))

still committed with CSE. The INDEX part of the formula will not be calculated if those two cells are empty, so it should speed things up - mind you, you are using full-column references, so shortening those to just cover your data may improve it further.

Hope this helps.

Pete

[dip11]

Ok I see, so I can only exclude the case based on the first 2 references (which reference a cell) but not those which reference a range (AB:AB), so that if its looping through AB&E it will ignore all those which have one of the two missing?

[Pete_UK]

It's the first parameter of your MATCH function - if that is empty then there is no point in looping through the arrays as there will be no match.

Pete

[dip11]

OK cheers for clearing that up. Might have to do it in reserve then

[Pete_UK]

Why don't you try it and see if there is any improvement in your workbook?

Pete

[NBVC]

Another option would be to pre-concatenate the cells in both tables, e.g. add a helper column in say ET which concatenates $ES2&$C2 with formula =$ES2&$C2 copied down

and then do the same for the other table...in say EU: =$AB2&$E2

then formula is simply

=IFERROR(INDEX(BF:BF,MATCH($ET2,$EU:$EU,0)),"")

regular entered and more efficient.