Lookup/Reference

[-Spax-]

I am trying to figure out the best way to approach the following task;

I'd like to build a function that will look at the sheet number, followed by value one, then two and return the data found in data columns 4 and 5.
The data is spread across 10+ sheets with tens of Value 1 values and more Value 2 values so I think I'd run out of IF's before I've gotten halfway.

Since being introduced to INDEX and MATCH by users on here they have become invaluable so I'm wondering if there are other undiscovered functions that I can learn!
When thinking about Indexes to this problem my head starts to hurt, in the workbook I'm using it would require about 12 indexes per sheet with 10+ sheets in the workbook

The attached example and screenshot below illustrates my issue (I hope).
Thanks,
Spax

[-Spax-]

Giving this a bump and re-adding attachments that were there earlier and had mysteriously disappeared

[Haseeb Avarakkan]

Hello Spax,

See the attached. Change your sheet name in A1. It should be exactly as your sheet names (NOT case sensitive). If you have any spaces in sheet names, enter same as in A1.

Hope this helps.

[-Spax-]

Haseeb, that's excellent! And a much smaller function than I could of imagined

Please Login or Register  to view this content.

A lot of the components within the function I'm not familiar with, could you explain how it works?

Thanks
Spax

[-Spax-]

After a few days off I returned to the function in the hope that it would make more sense but I'm still rather overwhelmed by it

Please Login or Register  to view this content.

Could someone run me through the above function? I want to learn how it works so I can use and adapt it in the future.
Thanks,
Spax