Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In Excel 2010 the forulma I am using is:


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




So in Excel 2003 the formula is now:


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

share|improve this question

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.

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

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


share|improve this answer
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. – Chris Stein Jul 26 '11 at 13:16

You must log in to answer this question.

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