Index Match where both row and column are dynamic

[jimbomcmucka]

Hey all,

This is my first post, but have used the forum loads in the past to find help with my excel problems. I'm hoping you can help with a problem. Forgive me if I am not explaining the issue very well.

I have created the below formula to retrieve some data from an extract, housed in a different tab:
=INDEX('OTMM ISM Data Extract'!1:1048576,MATCH(B2,'OTMM ISM Data Extract'!E:E,0),MATCH("Assembled Depth Value",'OTMM ISM Data Extract'!$A$1:$MA$1,0))

It works fine, however I have hit an issue. It turns out the extract that is dumped into the other tab doesn't order its columns consistently. So, I need a way to make this look up the row for the column named "GTIN".

Thanks in advance for any suggestions!!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=INDEX('OTMM ISM Data Extract'!A:MA,MATCH(B2,INDEX('OTMM ISM Data Extract'!A:MA,,MATCH("GTIN",'OTMM ISM Data Extract'!A1:MA1,0)),0),MATCH("Assembled Depth Value",'OTMM ISM Data Extract'!$A$1:$MA$1,0))

 

[jimbomcmucka]

Hi & welcome to MrExcel.
How about

=INDEX('OTMM ISM Data Extract'!A:MA,MATCH(B2,INDEX('OTMM ISM Data Extract'!A:MA,,MATCH("GTIN",'OTMM ISM Data Extract'!A1:MA1,0)),0),MATCH("Assembled Depth Value",'OTMM ISM Data Extract'!$A$1:$MA$1,0))

You are my hero!

Thanks so much, worked an absolute charm.

Cheers!
Jim

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Peter_SSs]

If interested, I think that you could shorten that to

=LET(r,'OTMM ISM Data Extract'!A:MA,r_1,INDEX(r,1,0),INDEX(r,MATCH(B2,INDEX(r,,MATCH("GTIN",r_1,0)),0),MATCH("Assembled Depth Value",r_1,0)))