Index, Match (Intersection cell data)

[Issyl2100]

Hello Everyone,


I'm trying to extract some data from cell intersection by using index and match functions, I have checked a lot of video and checked alot of forum posts but i can't do it.

I'll really appreciate your support.

Target cell is shown below (Green Highlighted)


https://imgur.com/a/rGFKSLa

 

[Jborg]

I cannot get the proper ranges in your sample Data.....but the formula is the below:
=INDEX(INDEX($H$4:$K$17,,MATCH($D$7,$H$3:$K$3)),MATCH($B$7&$C$7,INDEX($H$4:$H$17&$I$4:$I$17,0),0))

You do not require CSE. Just Enter.

 

[razaas]

Hello Everyone,


I'm trying to extract some data from cell intersection by using index and match functions, I have checked a lot of video and checked alot of forum posts but i can't do it.

I'll really appreciate your support.

Target cell is shown below (Green Highlighted)


https://imgur.com/a/rGFKSLa

Try this "=INDEX($J$4:$K$18,MATCH(B9:B9&C9:C9,$H$4:$H$18&$I$4:$I$18,0),MATCH(D9,$J$3:$K$3,0))" ctrl+shift+enter

 

[MARZIOTULLIO]

	B​	C​	D​	E​	F​	G​	H​	I​	J​
3​						Retail	Category	Apr-18​	May-18​
4​						Retail A	Cat1	124​	100​
5​						Retail A	Cat2	5​	24​
6​	Account	Category	Date	Value		Retail A	Cat3	46​	31​
7​	Retail A	Cat3	May-18​	31​		Retail A	Cat4	10​
8​						Retail A	Cat5
9​						Retail B	Cat1
10​						Retail B	Cat2
11​						Retail B	Cat3		50​
12​						Retail B	Cat4		40​
13​						Retail B	Cat5
14​						Retail C	Cat1
15​						Retail C	Cat2
16​						Retail C	Cat3
17​						Retail C	Cat4

<tbody>
</tbody>

E7=SUMPRODUCT(($G$4:$G$17=B7)*($H$4:$H$17=C7)*($I$3:$J$3=D7)*($I$4:$J$17))

 

[Sean15]

Excel 2010
	B	C	D	E	F	G	H	I	J	K
1
2							Name	Cat	04/01/18	05/01/18
3							Retail A	Cat1	124	100
4							Retail A	Cat2	5	24
5							Retail A	Cat3	46	31
6	Account	Category	Date	Value			Retail A	Cat4	10
7	Retail A	Cat3	05/01/18				Retail A	Cat5
8	Retail A	Cat4	04/01/18	10			Retail B	Cat1
Sheet1

Cell Formulas
Range	Formula
E7	=IFERROR(INDEX($J$3:$K$16,MATCH($B7,$H$3:$H$16,0), MATCH($C7,$I$3:$I$16,0),MATCH($D7,$J$2:$K$2,0)),"")
E8	=SUMPRODUCT(($H$3:$H$16=$B8)*($I$3:$I$16=$C8)*($J$2:$K$2=$D8)*($J$3:$K$16))

Just curious, why is formula in E7 above returning "" and not 31.

 

[Sean15]

Hi:

The INDEX and MATCH code in post#5 should return 31 but it it returning "". Could you take a look and advise please?

 

[Aladin Akyurek]

Hi:

The INDEX and MATCH code in post#5 should return 31 but it it returning "". Could you take a look and advise please?

In E7 control+shift+enter, not just enter, and copy down:

=INDEX($I$3:$J$8,MATCH(A7,IF($H$3:$H$8=B7,$G$3:$G$8),0),MATCH(C7,$I$2:$J$2,0))

 

[Sean15]

Thank you.