An array using vlookup/match or index/match or other pulling from multiple sheets.

This formula partially works:
=IFERROR(INDEX('Item (1)'!$G$17:$G$24,MATCH(G4,'Item (1)'!$B$17:$B$24,0)),0)

But I must manually create it for each Item sheet and copy it across to the right.

In your posted file G4 appears in "Item(2)" sheet [ although it could be selected in Item(1) ? ]so I do not see any correlation between Item sheets and F4:BH4 data

Originally Posted by JohnTopley

In your posted file G4 appears in "Item(2)" sheet [ although it could be selected in Item(1) ? ]so I do not see any correlation between Item sheets and F4:BH4 data

While there could be 50+ options across the Data_new row 4 (sourced from the Value Drivers tab), each Item page will only have 3-6 data points.

Does that make sense?

I've updated the spreadsheet with the manual formula.

How many types of Item (XXX) are there? Five, as in your sample - being Item (1) to Item (5) - or what?

You can use INDIRECT, if there are lots of them, but your sheet WILL slow down.

Originally Posted by Glenn Kennedy

You can use INDIRECT, if there are lots of them, but your sheet WILL slow down.

Five for the demo, could be 50+
Speed isn't a big issue

I finally got it by bringing in an array of the sheet names as a column and using this formula:

Formula:

Please Login or Register  to view this content.

And thanks to Glen who got me in the right direction. However, I had to mess with it to get it working.

Is there a way to mark the thread as solved? (figured that one)

Mike