Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 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.

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
add comment

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

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.