INDEX/MATCH with moving data and 2 column headers

[Nyx*]

Okay, I did a quick search on the forum in order to confirm wether someone had met the same problem or not, but didn't find anything.

So basically, the problem here is down to the proper use of INDEX/MATCH, but to give some backstory, here is my problem: I'm trying, through excel, to achieve results that were previously done by SQL requests on a database. I understand that, without VBA, I migh be asking too much. To make long story short, it was previously possible to extract any and all data from the database, but for the time being, I'm stuck with extractions generated in a way I cannot modify.

As you will see in the attached file, I'm working on expenses and profits, trying to compare different fiscal years. All accounts, may they be on the expense side (beginning with 6) or the profits side (beginning with 7), are always listed in the same B column. But depending on the structure I work on, it might not use the whole array of account. And if an account is not used, it is not simply filled with 0, it will rather not appear.

Same goes for the activity columns: in the attached example, there are only 2, COMM and OPS. But you could have much more, it all depends on the way the structure decided allocate its accounts. Again, it's the same mechanic used for the accounts: if an activity code is not used, it does not appear. This means that a code like OPS, which is always used, will always see its location within the spreadsheet change depending on the structure. As you can see, under an activity code, there are always the same 4 subcolumns, and thus, the activity code cell is a merged one.

In the end, what I'm looking for is Cell C3 in 'Analysis'!: a way to INDEX/MATCH a data with an evolving line, where I only take the "prefix" of the account, and an evolving column, with 2 headers.
INDEX/MATCH with 2 MATCH conditions seemed the way to go, but I might be wrong from the beginning.

[JohnTopley]

in C3

=INDEX('Data 2020'!$B$3:$I$17,MATCH("6 / *",'Data 2020'!$A$3:$A$22,0),MATCH("COMM",'Data 2020'!$B$1:$I$1,0)+MATCH("Expenses (made)",'Data 2020'!$B$2:$I$2)-1)

in C4

=INDEX('Data 2020'!$B$3:$I$17,MATCH("6 / *",'Data 2020'!$A$3:$A$22,0),MATCH("OPS",'Data 2020'!$B$1:$I$1,0)+MATCH("Expenses (made)",'Data 2020'!$B$2:$I$2)-1)

[Nyx*]

Hello John,

Thank you for your answer, works fine ! If I understand correctly, the 2 MATCH had to be linked with a + and not a *. I didn't know a * could replace a LEFT, nice catch. Btw, why did you add -1 at the end ?

[JohnTopley]

=INDEX('Data 2020'!$B$3:$I$17,MATCH("6 / *",'Data 2020'!$A$3:$A$22,0),MATCH("COMM",'Data 2020'!$B$1:$I$1,0)+MATCH("Expenses (made)",'Data 2020'!$B$2:$I$2)-1)

The first MATCH (in RED) will return 1 (matches column B which is the first in the "lookup" range $B$1:$I$1: the second MATCH (BOLD) will return 2 as we match on column C so 1+2 =3 but we want column C (which is second column in range $B$2:$I$2) so we subtract 1.

Hope this helps.

[Nyx*]

Indeed, that helps a lot, thank you ! Which means I must adjust that last number depending on which subcolumn I'm looking for, right ?

[JohnTopley]

Yes because you are first finding "COMM" or "OPS" and same column (next line down) is also the first sub-category.