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).

 

[Sergius]

Install XL2BB and provide a data snippet.

 

[JohnMense]

See below post instead!

 

[JohnMense]

Otherwise I have this?

INDEX-MATCH.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Function	Practice	Team A			Team B
4			Baseline	Target	Current	Baseline	Target	Current
5	Function 1	Strategy	0.5	3.0	2.0	3.0	3.0	3.0		Team	Function	Practice	Baseline	Target	Current
6	Function 1	Compliance	2.6	1.5	3.0	1.8	2.0	1.8		Team A	Function 1	Strategy
7	Function 1	Education	1.5	2.0	1.5	1.4	2.5	1.4		Team A	Function 1	Compliance
8	Function 2	Threats	0.0	1.0	0.0	0.1	2.3	0.1		Team A	Function 1	Education
9	Function 2	Security	1.0	2.0	1.0	2.0	2.0	2.0		Team A	Function 2	Threats
10	Function 2	Architecture	0.5	2.0	0.5	1.8	1.8	1.8		Team A	Function 2	Security
11	Function 3	Build	1.0	2.5	1.9	2.4	3.0	2.4		Team A	Function 2	Architecture
12	Function 3	Deploy	1.5	2.5	1.9	1.5	2.5	1.5		Team A	Function 3	Build
13	Function 3	Manage	3.0	3.0	3.0	1.3	1.8	1.3		Team A	Function 3	Deploy
14	Function 4	Assessment	0.0	2.0	0.0	1.6	2.0	1.6		Team A	Function 3	Manage
15	Function 4	Testing	0.9	1.5	0.9	2.8	2.5	2.8		Team A	Function 4	Assessment
16	Function 4	Validation	0.5	2.0	0.5	1.6	3.0	1.6		Team A	Function 4	Testing
17	Function 5	Incidents	0.1	2.5	2.8	1.3	2.5	1.3		Team A	Function 4	Validation
18	Function 5	Environment	0.0	2.5	1.0	2.3	3.0	2.3		Team A	Function 5	Incidents
19	Function 5	Operations	0.6	2.5	0.6	1.6	3.0	1.6		Team A	Function 5	Environment
20										Team A	Function 5	Operations
21										Team B	Function 1	Strategy
22										Team B	Function 1	Compliance
23										Team B	Function 1	Education
24										Team B	Function 2	Threats
25										Team B	Function 2	Security
26										Team B	Function 2	Architecture
27										Team B	Function 3	Build
28										Team B	Function 3	Deploy
29										Team B	Function 3	Manage
30										Team B	Function 4	Assessment
31										Team B	Function 4	Testing
32										Team B	Function 4	Validation
33										Team B	Function 5	Incidents
34										Team B	Function 5	Environment
35										Team B	Function 5	Operations
Data

 

[JohnMense]

I believe the merged 'Team' cell is the root of the issue. A match for Team A will always return result '4' so when I try to then match location for the value header (ex: baseline) it causes a problem.

 

[Alex Blakenburg]

Although we prefer to avoid Merged Cells and would have used Center Across Selection, it is the fact that Team only appears in the first column of the "3 column set" that is causing you grief.
Assuming both source and destination have the same 3 columns in the same order you can try these in Cell N6.
1) If not a table and you can spill across the row:
Just copy down to all rows:

=INDEX($D$5:$I$19,
       MATCH(1,($B$5:$B$19=$L6)*($C$5:$C$19=$M6),0),
       MATCH($K6,$D$3:$I$3,0)+{0,1,2})

2) If it is a table or you just don't want to spill:
Copy across and down

=INDEX($D$5:$I$19,
       MATCH(1,($B$5:$B$19=$L6)*($C$5:$C$19=$M6),0),
       MATCH($K6,$D$3:$I$3,0)-1+COLUMNS($N$5:N$5))

Basically once you find the Team you want to advance 0, 1, or 2 columns from that position.

 

[JohnMense]

Gives me errors unfortunately and no clue why.

(MATCH($K6,$D$3:$I$3,0)-1+COLUMNS($N$5:N$5)): I see that it returns the column number desired for the 3 value fields, thanks for that!

Have tried to then MATCH to the Function value location and return the value but no luck at all.

Any other ideas?

 

[Alex Blakenburg]

Please provide an XL2BB of the sheet that has the formula in place. Unless you don't actually have MS365 the below should show you that it works.

20241128 IndexMatch Offset from hdg JohnMense.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3		Function	Practice	Team A			Team B
4				Baseline	Target	Current	Baseline	Target	Current
5		Function 1	Strategy	0.5	3	2	3	3	3		Team	Function	Practice	Baseline	Target	Current
6		Function 1	Compliance	2.625	1.5	3	1.75	2	1.75		Team A	Function 1	Strategy	0.5	3	2
7		Function 1	Education	1.5	2	1.5	1.375	2.5	1.375		Team A	Function 1	Compliance	2.625	1.5	3
8		Function 2	Threats	0	1	0	0.125	2.25	0.125		Team A	Function 1	Education	1.5	2	1.5
9		Function 2	Security	1	2	1	2	2	2		Team A	Function 2	Threats	0	1	0
10		Function 2	Architecture	0.5	2	0.5	1.75	1.75	1.75		Team A	Function 2	Security	1	2	1
11		Function 3	Build	1	2.5	1.875	2.375	3	2.375		Team A	Function 2	Architecture	0.5	2	0.5
12		Function 3	Deploy	1.5	2.5	1.875	1.5	2.5	1.5		Team A	Function 3	Build	1	2.5	1.875
13		Function 3	Manage	3	3	3	1.25	1.75	1.25		Team A	Function 3	Deploy	1.5	2.5	1.875
14		Function 4	Assessment	0	2	0	1.625	2	1.625		Team A	Function 3	Manage	3	3	3
15		Function 4	Testing	0.875	1.5	0.875	2.75	2.5	2.75		Team A	Function 4	Assessment	0	2	0
16		Function 4	Validation	0.5	2	0.5	1.625	3	1.625		Team A	Function 4	Testing	0.875	1.5	0.875
17		Function 5	Incidents	0.125	2.5	2.75	1.25	2.5	1.25		Team A	Function 4	Validation	0.5	2	0.5
18		Function 5	Environment	0	2.5	1	2.25	3	2.25		Team A	Function 5	Incidents	0.125	2.5	2.75
19		Function 5	Operations	0.625	2.5	0.625	1.625	3	1.625		Team A	Function 5	Environment	0	2.5	1
20											Team A	Function 5	Operations	0.625	2.5	0.625
21											Team B	Function 1	Strategy	3	3	3
22											Team B	Function 1	Compliance	1.75	2	1.75
23											Team B	Function 1	Education	1.375	2.5	1.375
24											Team B	Function 2	Threats	0.125	2.25	0.125
25											Team B	Function 2	Security	2	2	2
26											Team B	Function 2	Architecture	1.75	1.75	1.75
27											Team B	Function 3	Build	2.375	3	2.375
28											Team B	Function 3	Deploy	1.5	2.5	1.5
29											Team B	Function 3	Manage	1.25	1.75	1.25
30											Team B	Function 4	Assessment	1.625	2	1.625
31											Team B	Function 4	Testing	2.75	2.5	2.75
32											Team B	Function 4	Validation	1.625	3	1.625
33											Team B	Function 5	Incidents	1.25	2.5	1.25
34											Team B	Function 5	Environment	2.25	3	2.25
35											Team B	Function 5	Operations	1.625	3	1.625
Data

Cell Formulas
Range	Formula
N6:P35	N6	=INDEX($D$5:$I$19, MATCH(1,($B$5:$B$19=$L6)*($C$5:$C$19=$M6),0), MATCH($K6,$D$3:$I$3,0)-1+COLUMNS($N$5:N$5))

 

[JohnMense]

INDEX-MATCH.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Function	Practice	Team A			Team B
4			Baseline	Target	Current	Baseline	Target	Current
5	Function 1	Strategy	0.5	3.0	2.0	3.0	3.0	3.0		Team	Function	Practice	Baseline	Target	Current
6	Function 1	Compliance	2.6	1.5	3.0	1.8	2.0	1.8		Team A	Function 1	Strategy	#N/A	#N/A	#N/A
7	Function 1	Education	1.5	2.0	1.5	1.4	2.5	1.4		Team A	Function 1	Compliance	#N/A	#N/A	#N/A
8	Function 2	Threats	0.0	1.0	0.0	0.1	2.3	0.1		Team A	Function 1	Education	#N/A	#N/A	#N/A
9	Function 2	Security	1.0	2.0	1.0	2.0	2.0	2.0		Team A	Function 2	Threats	#N/A	#N/A	#N/A
10	Function 2	Architecture	0.5	2.0	0.5	1.8	1.8	1.8		Team A	Function 2	Security	#N/A	#N/A	#N/A
11	Function 3	Build	1.0	2.5	1.9	2.4	3.0	2.4		Team A	Function 2	Architecture	#N/A	#N/A	#N/A
12	Function 3	Deploy	1.5	2.5	1.9	1.5	2.5	1.5		Team A	Function 3	Build	#N/A	#N/A	#N/A
13	Function 3	Manage	3.0	3.0	3.0	1.3	1.8	1.3		Team A	Function 3	Deploy	#N/A	#N/A	#N/A
14	Function 4	Assessment	0.0	2.0	0.0	1.6	2.0	1.6		Team A	Function 3	Manage	#N/A	#N/A	#N/A
15	Function 4	Testing	0.9	1.5	0.9	2.8	2.5	2.8		Team A	Function 4	Assessment	#N/A	#N/A	#N/A
16	Function 4	Validation	0.5	2.0	0.5	1.6	3.0	1.6		Team A	Function 4	Testing	#N/A	#N/A	#N/A
17	Function 5	Incidents	0.1	2.5	2.8	1.3	2.5	1.3		Team A	Function 4	Validation	#N/A	#N/A	#N/A
18	Function 5	Environment	0.0	2.5	1.0	2.3	3.0	2.3		Team A	Function 5	Incidents	#N/A	#N/A	#N/A
19	Function 5	Operations	0.6	2.5	0.6	1.6	3.0	1.6		Team A	Function 5	Environment	#N/A	#N/A	#N/A
20										Team A	Function 5	Operations	#VALUE!	#VALUE!	#VALUE!
21										Team B	Function 1	Strategy	#VALUE!	#VALUE!	#VALUE!
22										Team B	Function 1	Compliance	#VALUE!	#VALUE!	#VALUE!
23										Team B	Function 1	Education	#VALUE!	#VALUE!	#VALUE!
24										Team B	Function 2	Threats	#VALUE!	#VALUE!	#VALUE!
25										Team B	Function 2	Security	#VALUE!	#VALUE!	#VALUE!
26										Team B	Function 2	Architecture	#VALUE!	#VALUE!	#VALUE!
27										Team B	Function 3	Build	#VALUE!	#VALUE!	#VALUE!
28										Team B	Function 3	Deploy	#VALUE!	#VALUE!	#VALUE!
29										Team B	Function 3	Manage	#VALUE!	#VALUE!	#VALUE!
30										Team B	Function 4	Assessment	#VALUE!	#VALUE!	#VALUE!
31										Team B	Function 4	Testing	#VALUE!	#VALUE!	#VALUE!
32										Team B	Function 4	Validation	#VALUE!	#VALUE!	#VALUE!
33										Team B	Function 5	Incidents	#VALUE!	#VALUE!	#VALUE!
34										Team B	Function 5	Environment	#VALUE!	#VALUE!	#VALUE!
35										Team B	Function 5	Operations	#VALUE!	#VALUE!	#VALUE!
36
Data

Cell Formulas
Range	Formula
N6:P35	N6	=INDEX($D$5:$I$19,MATCH(1,($B$5:$B$19=$L6)*($C$5:$C$19=$M6),0),MATCH($K6,$D$3:$I$3,0)-1+COLUMNS($N$5:N$5))

 

[JohnMense]

I believe you, just no clue why I do not get the same result.... ARGGGHHH....