Combining MID, MATCH and INDIRECT

[Bartonsen]

What is wrong here?
I thought I got it right when combining MATCH and INDIRECT (Big Thanks; Croweater!), and on the Summary sheet attached, the code applied to A2:H4 is correct.
But when similar code is applied to Summary sheet AQ4:AZ6, it does not work?

The code that works in CELL D2:
=MID("abc",MATCH("x",INDIRECT("'"&$A2&"'!G"& MATCH(D$1,INDIRECT("'"&$A2&"'!E3:E7"),0)+2&":I"&MATCH(D$1,INDIRECT("'"&$A2&"'!E3:E7"),0)+2),0),1)

The code that doesn't work in CELL AQ4:
=MID("abc",MATCH("x",INDIRECT("'"&$L4&"'!I"& MATCH(AQ$2,INDIRECT("'"&$L4&"'!G47:G82"),0)+2&":K"&MATCH(AQ$2,INDIRECT("'"&$L4&"'!G47:G82"),0)+2),0),1)

Summary sheet A10:J13 shows how AQ4:AZ6 should look like.
Please note the order of G47:G56 (1,2,3,4,7,6,5,8,10,9).

[Bernie Deitrick]

The +2 was to account for the first row of your table being in row 3 - so change the +2 to +46 because your first row of the table is 47:


=MID("abc",MATCH("x",INDIRECT("'"&$A11&"'!I"& MATCH(B$10,INDIRECT("'"&$A11&"'!G47:G56"),0)+46&":K"&MATCH(B$10,INDIRECT("'"&$A11&"'!G47:G56"),0)+46),0),1)

If your column G would not have any matches other than in your table, you could look at the entire column without an offset:

MATCH(B$10,INDIRECT("'"&$A11&"'!G47:G56"),0)+46
MATCH(B$10,INDIRECT("'"&$A11&"'!G:G"),0)

[Bartonsen]

Fantastic Bernie! Thanks a lot!

[Bo_Ry]

With not many sheets I would use Choose

=MID("abc",MATCH("x",INDEX(VLOOKUP(B$10,CHOOSE(MATCH($A11,$L$4:$L$6,),'steven little'!$G$47:$K$56,john!$G$47:$K$56,'tom jones'!$G$47:$K$56),{3,4,5},),),),1)

instead of Volatile INDIRECT

=MID("abc",MATCH("x",INDEX(VLOOKUP(B$10,INDIRECT("'"&$A11&"'!G47:K56"),{3,4,5},),),),1)

[Croweater]

Why have you posted EXACTLY the same problem which I have already answered in detail here?

https://www.excelforum.com/excel-gen...ml#post5525292

[Bernie Deitrick]

It is not the EXACT same problem - he was asking for your solution to be applied to a different range, and was having problems doing that.

[Croweater]

of course it's the same 'problem'! What would happen if everyone created a new thread when they couldn't apply a formula to a different range?
Isn't that why there is a 'forum rule' for this?

He asked the same question in the original thread too, but this time couldn't be bothered waiting for an answer, so just created a new thread.

This leads to people (i.e. me) wasting their time 'solving' problems on one thread that have already been solved on a new one!