"Excel Ran Out of Resources" ... =INDEX MATCH Alternatives?

Hi folks,

I am trying to use the following formula:

=INDEX('extract'!A1:AE539,MATCH(1,(extract'!B:B=B3)*('extract'!C:C=E3)*('extract'!1:1=P2),0),1)

However once I hit Command + Shift + Enter I receive the following error message:

"Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated."

I don't understand why ... there's only 539 rows of data, the size of the entire spreadsheet is 400kb!

Is there an alternative to =INDEX MATCH that I can use?

Update: I have just tried this with an even smaller table of data and still receive the same error message. It CANNOT be an issue of overworking Excel ... so what is the issue? I'm absolutely stumped guys!

Whole column references means that Excel is calculating much more than 1,000,000 rows. There are formulae (SUMIF, COUNTIF, some INDEX formulae) where it's OK. Otherwise, don't use em.

Now a sheet would be useful.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

That should be it sorted...

Originally Posted by wmjenner

The criteria in the above are all columns. Can I add a ROW as a fourth criteria (I want to match to a specific date)? I'm not sure how to distinguish rows and columns. Thanks

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread