INDEX-MATCH-MATCH help!

[JohnMense]

Hi everyone

Having major issue in getting INDEX-MATCH-MATCH to locate specific data and return it to another sheet.

Data tab:
1. 2 teams, with the names running across merged cells above the 3 columns for the data fields
2. B = 5 Functions 3x each, C = 3 Practices per Function
3. 3 value fields under each team for Baseline, Target, Current values. These numbers all get derived without issue.

Index-Match tab:
Trying to pull into a table all the values above with INDEX-MATCH for then using in a pivot table.

With MATCH I am able to locate the team and practice and function individually but when I try and INDEX with variations of the matches I am not getting the right info. In the Baseline (1st data column) I am ok but then cannot get the target or current values right.

I have tried to first locate the Team, then the value header (Baseline, etc),and then Practice but is not working out.

Not looking to use macros or anything, just with INDEX & MATCH functions.

Any help greatly appreciated!

Note: this is a simple extract, actual file has many teams with more added weekly....

Here are images of the 2 tabs. Not sure how to publish the actual Excel sheets (if possible at all).

 

[JohnMense]

So your comment about 365 hit home and I tried it on my work PC which is 365 and it works...

So ultimately that is a solution, thank you so much, but I do not understand why on my home system Pro 2019 it doesn't.

 

[JohnMense]

Regardless, this is for work so I thank you very much!

 

[Fluff]

With 2019 you need to confirm the formula with Ctrl Shift Enter, rather than just Enter.

 

[Alex Blakenburg]

Fluff beat me but I'll post the below anyway.

Using the Match function in this way

MATCH(1,($B$5:$B$19=$L6)*($C$5:$C$19=$M6),0)

requires dynamic array capability it might work if you enter the formula as Ctrl+Shift+Enter but that is going to be painful since you are going to be using it in multiple rows and columns.
If you need it to work on 2019, the easiest would be to introduce a helper column in the source data that concatenates Function & Practice.
One other thought is that I haven't seen any practice that is in more than one function. If that is the case we could match just on practice which would circumvent needing to look up 2 values and a standard match could be used.

 

[henrik2h]

An alternative solution would be a helper row (between row 4 and 5) where you concatenate Team & Baseline and use that in your Match-function.

 

[henrik2h]

An alternative solution would be a helper row (between row 4 and 5) where you concatenate Team & Baseline and use that in your Match-function.

too late..