INDEX MATCH Across Multiple Sheets

[B1313]

This has been posted a few times before but the answer has never been definitive nor has it been dynamic to work in a multitude of situations.

Sheets:

1. DataSheetA
2. DatasheetB
3. DataSheetC
4. DataSheetD

Starting Point: $C$10:$C$999

=INDEX(DataSheetA:DataSheetD:$E$10:$E$999, MATCH($C10, DataSheetA:DataSheetD:$C$10:$C$999, 0))

Obviously this will not work because Excel cannot accommodate these kind of 3-D (or rather 4-D?) references. This should explain the goal though. Whenever a value, that is not blank, is found on a sheet then stop and return that value. This is the goal. Simple enough, but everyone and their mother loves VLOOKUP to do this, which I LOATHE, so nobody has made a good INDEX/MATCH version.

I found a somewhat useful UDF for looking up across multiple sheets via VLOOKUP, but not only is it slow as heck, it causes circular reference errors in worksheets if they are made using the same cells/columns/rows for specific data.

Can someone help me? Is there another way to do this? I did try nested IFERRORS & INDEX/MATCH for each sheet, which works, until the return value is "" which causes the rest of the IFERRORS to never fire off. The solution is a monstrous bunch of nested if statements...yucky.

Please and thanks,

 

[Aladin Akyurek]

Care to post the formula that you would use if you had just DataSheetA?

 

[B1313]

Care to post the formula that you would use if you had just DataSheetA?

Absolutely!

=IFERROR(INDEX(DataSheetA!E$10:E$999, MATCH($C10, DataSheetA!$C$10:$C$999, 0)), "ERROR")

 

[Aladin Akyurek]

Absolutely!

=IFERROR(INDEX(DataSheetA!E$10:E$999, MATCH($C10, DataSheetA!$C$10:$C$999, 0)), "ERROR")

Create a range for the names of the data sheets DataSheetA, etc., select this range, and name the selection SheetList.

Once done so, control+shift+enter, not just enter:

=VLOOKUP($C10,INDIRECT("'"&INDEX(SheetList,
    MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetList&"'!C10:C999"),$C10)>0,0))&"'!C10:E999"),3,0)

If so desired, you can wrap the foregoing formula into an IFNA or an IFERROR call.

 

[B1313]

Create a range for the names of the data sheets DataSheetA, etc., select this range, and name the selection SheetList.

Once done so, control+shift+enter, not just enter:

=VLOOKUP($C10,INDIRECT("'"&INDEX(SheetList,
    MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetList&"'!C10:C999"),$C10)>0,0))&"'!C10:E999"),3,0)

If so desired, you can wrap the foregoing formula into an IFNA or an IFERROR call.

While that is not a bad answer, it uses 2 things which will DRAMATICALLY reduce performance. Array formulas (AKA Evil) and VLOOKUP (which I want to avoid at all costs as it renders the entire array each cycle through which increases the array size roughly twice over (since two columns will be added in). This formula would be applied down numerous cells so array formulas become extremely problematic.

 

[Aladin Akyurek]

While that is not a bad answer, it uses 2 things which will DRAMATICALLY reduce performance. Array formulas (AKA Evil) and VLOOKUP (which I want to avoid at all costs as it renders the entire array each cycle through which increases the array size roughly twice over (since two columns will be added in). This formula would be applied down numerous cells so array formulas become extremely problematic.

If you are worried about performance, bring all of your data into a single sheet (say Data), sort the data area on column C in ascending order, and invoke the following:

=IF(LOOKUP($C10,Data!C:C)=$C10,LOOKUP($C10,Data!C:C,Data!E:E),"")

and enjoy the performance.

 

[B1313]

If you are worried about performance, bring all of your data into a single sheet (say Data), sort the data area on column C in ascending order, and invoke the following:

=IF(LOOKUP($C10,Data!C:C)=$C10,LOOKUP($C10,Data!C:C,Data!E:E),"")

and enjoy the performance.

The idea is to pull all data from separate sheets into 1 WITHOUT doing it manually or deleting other sheets, why would I want to take index'ed values from multiple sheets unless I was consolidating them. The formula I wanted would take X number of sheets and a range to be looked at in each then pull the respective value to be indexed from each, all while the sheets included could be subjected to criteria. However, as I stated above and in the title, I would like INDEX MATCH options as the LOOKUP functions are horrific and will not work in this case (I tested them out before and they are sh**) as the formula would be applied to well over 500 cells so (500 ^ 500 * 5 ^ 6 is how many cells will be calculating and recalculating) the solution needs to be efficient and performance focused.

I managed to do something similar with IFS as it's VERY effective to eliminate clutter prone nested IF statements, the issue is that is not as efficient as it could be (and very few people use Office 2016 (for IDK what reason)). The formula is still several lines long. I figured another formula or function would be better suited. I am not the first person to come across this limit in Excel. I see, however, I am the first to not want to use volatile functions or formulas.

I think I would start looking at UDF's as they are more efficient than draconian formulas.

 

[Aladin Akyurek]

I disagree with your claims in post #5 and #7... That array formulas are evil, (V)LOOKUP is inefficient/horrific, and you are "the first to not want to use volatile functions or formulas." But I won't argue. By the way, good luck with UDF's.

 

[FDibbins]

All I can add is - wow