A formula to do a 3 way match and bring a value

[bamboo1975]

Hi Lovely people,

Can someone please help me with this

I need a formula in I14 to match the I12 with row2 AND I13 with row 3 and E14 with A4:A9 and bring me the corresponding value. Please have a look at the small screenshot I've attached in the below link. Cheers.

https://1drv.ms/u/s!AmwCZnz5Xsnh7lledvHsOC-kXC_K?e=RdwxEp

 

[Eric W]

Option 1:

Select the I15:L15 range and enter this formula:

=INDEX(OFFSET(E4:H9,0,MATCH(I12,E2:AO2,0)-1),MATCH(E14,A4:A9,0),0)

change the ranges to match your sheet, especially the E2:AO2 range since I don't know how far to the right it goes, and confirm by pressing Control+Shift+Enter.


Option 2:

Enter this formula in I14:

=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),COLUMNS($I15:I15))

Change the ranges as before, but it's an ordinary formula, so just use Enter. Copy it to the next 3 cells.

 

[bamboo1975]

Hi Eric,

The second one works and bring me a number (563 in this example) but when I copy it to J14 and K14 it still brings me the same value. Its expected to bring the value that matches the top cell (J13 and J14) which in this example is 252 in J14. Cheers.

Regards,
Bobby

 

[Eric W]

Did you copy the original formula, or type it in? A missing or extra $ can make a huge difference. Here's the test sheet I used (and you may consider using one of the screen printing tools, such as the HTML Maker in my signature, in the future to make it easier for people to help):

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2					Wed, 04-Sep-19				Wed, 11-Sep-19
3					KPI 1000	KPI 1400	KPI 1800	Action	KPI 1000	KPI 1400	KPI 1800	Action
4					223			223	223			223
5					201			201	201			201
6								0				0
7					449	233		682	449	233		682
8	Perveen Nawaz, Usama				563	252		815	1	2	3	4
9					765	397		1162	765	397		1162
10
11
12									Wed, 04-Sep-19
13									KPI 1000	KPI 1400	KPI 1800	Action
14					Perveen Nawaz, Usama				563	252	0	815
15									563	252	0	815
16									563	252	0	815
Sheet9

Cell Formulas
Range	Formula
I15	=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),COLUMNS($I15:I15))
I16	=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),MATCH(I$13,$E$3:$H$3,0))
I14:L14	{=INDEX(OFFSET(E4:H9,0,MATCH(I12,E2:AO2,0)-1),MATCH(E14,A4:A9,0),0)}
J15	=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),COLUMNS($I15:J15))
J16	=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),MATCH(J$13,$E$3:$H$3,0))
K15	=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),COLUMNS($I15:K15))
K16	=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),MATCH(K$13,$E$3:$H$3,0))
L15	=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),COLUMNS($I15:L15))
L16	=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),MATCH(L$13,$E$3:$H$3,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Option 1 is the I14:L14 array formula. I15 is option 2, with J15:L15 showing what they should be after copying I15. I added the row 16 formulas, which use MATCH instead of COLUMNS to determine which heading to use. Hope this helps.

 

[bamboo1975]

Works now. Problem solved. Cheers Eric you're a star

Did you copy the original formula, or type it in? A missing or extra $ can make a huge difference. Here's the test sheet I used (and you may consider using one of the screen printing tools, such as the HTML Maker in my signature, in the future to make it easier for people to help):

	A	B	C	D	E	F	G	H	I	J	K	L
Wed, 04-Sep-19	Wed, 11-Sep-19
KPI 1000	KPI 1400	KPI 1800	Action	KPI 1000	KPI 1400	KPI 1800	Action
Perveen Nawaz, Usama
Wed, 04-Sep-19
KPI 1000	KPI 1400	KPI 1800	Action
Perveen Nawaz, Usama

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]223[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]223[/TD]
[TD="align: right"]223[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]223[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]201[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]201[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]201[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]449[/TD]
[TD="align: right"]233[/TD]
[TD="align: right"][/TD]
[TD="align: right"]682[/TD]
[TD="align: right"]449[/TD]
[TD="align: right"]233[/TD]
[TD="align: right"][/TD]
[TD="align: right"]682[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]563[/TD]
[TD="align: right"]252[/TD]
[TD="align: right"][/TD]
[TD="align: right"]815[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]765[/TD]
[TD="align: right"]397[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1162[/TD]
[TD="align: right"]765[/TD]
[TD="align: right"]397[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1162[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]563[/TD]
[TD="align: right"]252[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]815[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]563[/TD]
[TD="align: right"]252[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]815[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]563[/TD]
[TD="align: right"]252[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]815[/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I15[/TH]
[TD="align: left"]=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),COLUMNS($I15:I15))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J15[/TH]
[TD="align: left"]=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),COLUMNS($I15:J15))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K15[/TH]
[TD="align: left"]=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),COLUMNS($I15:K15))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L15[/TH]
[TD="align: left"]=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),COLUMNS($I15:L15))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I16[/TH]
[TD="align: left"]=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),MATCH(I$13,$E$3:$H$3,0))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J16[/TH]
[TD="align: left"]=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),MATCH(J$13,$E$3:$H$3,0))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K16[/TH]
[TD="align: left"]=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),MATCH(K$13,$E$3:$H$3,0))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L16[/TH]
[TD="align: left"]=INDEX(OFFSET($E$4:$H$9,0,MATCH($I$12,$E$2:$AO$2,0)-1),MATCH($E$14,$A$4:$A$9,0),MATCH(L$13,$E$3:$H$3,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I14:L14[/TH]
[TD="align: left"]{=INDEX(OFFSET(E4:H9,0,MATCH(I12,E2:AO2,0)-1),MATCH(E14,A4:A9,0),0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Option 1 is the I14:L14 array formula. I15 is option 2, with J15:L15 showing what they should be after copying I15. I added the row 16 formulas, which use MATCH instead of COLUMNS to determine which heading to use. Hope this helps.

 

[Eric W]

Glad it works for you.