1

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?

2 Answers 2

2

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.

2
  • I really like your explaining what the formula is doing and the suggested improvement, and reasoning behind it. nicely answered +1
    – datatoo
    Aug 5, 2011 at 16:17
  • Thanks for the help. I'd vote for this but my rep isn't high enough. Aug 8, 2011 at 14:41
0

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

1
  • It's the curse of working with someone elses work, the guy who created this has left. I may need to pull this apart it looks to be more complex than is needed. Jul 26, 2011 at 13:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .