Converting a formula from MS Excel 2010 to MS Excel 2003?

Question

In Excel 2010 the forulma I am using is:

=_xlfn.IFERROR(INDEX(K$76:K$2044,MATCH(Q23,P$76:P$2044,0),1),INDEX(K$76:K$2044,MATCH(Q23,L$76:L$2044,0),1))

But I need to convert it to excel 2003. I have figured out:

_xlfn.IFERROR

Becomes

_IF(ISERROR)

So in Excel 2003 the formula is now:

=IF(ISERROR(INDEX(K$76:K$2044,MATCH(Q23,P$76:P$2044,0),1),INDEX(K$76:K$2044,MATCH(Q23,L$76:L$2044,0),1)))

But there are too many agruments for 2003 to handle. Could anyone help to shorten or simplify the formula?

Answer 1

If I understand the logic correctly, you want to: Look for Q23 in P76:2044 If it's there, do an index from K based on that position, else do an index from K based on a match against L76:2044 (and trust that a match exists).

If that's right, then I would move the error trap to the MATCH function, rather than the INDEX, like so:

=INDEX(K$76:K$2044, IF(ISERROR(MATCH(Q23,P$76:P$2044,0)), MATCH(Q23,L$76:L$2044,0), MATCH(Q23,P$76:P$2044,0)) ,1)

(I've added some extra spaces to make it slightly clearer). This should make it easier to debug, and probably more efficient to calculate since it only does two matches and one index, rather than 2 and 2 (although index is pretty efficient).

Since the array for the index is a single column, you could drop the final ",1", although personally I like the clarity it provides that this is definitely pulling a value from the first column.

Incidentally, this will still break if Q23 is not found in L nor P. Maybe that can't happen in your dataset, but just beware.

Answer 2

You're missing a bracket to close off the ISERROR function, and you haven't specified what you want if there isn't an error. Perhaps repeating your first index lookup:-

=IF(ISERROR(INDEX(K$76:K$2044,MATCH(Q23,P$76:P$2044,0),1)),INDEX(K$76:K$2044,MATCH(Q23,L$76:L$2044,0),1)),INDEX(K$76:K$2044,MATCH(Q23,P$76:P$2044,0),1))