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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Retail[/TD]
[TD]Category[/TD]
[TD]

Apr-18​

[/TD]
[TD]

May-18​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Retail A[/TD]
[TD]Cat1[/TD]
[TD]

124​

[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Retail A[/TD]
[TD]Cat2[/TD]
[TD]

5​

[/TD]
[TD]

24​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Account[/TD]
[TD]Category[/TD]
[TD]Date[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD]Retail A[/TD]
[TD]Cat3[/TD]
[TD]

46​

[/TD]
[TD]

31​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Retail A[/TD]
[TD]Cat3[/TD]
[TD]

May-18​

[/TD]
[TD]

31​

[/TD]
[TD][/TD]
[TD]Retail A[/TD]
[TD]Cat4[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Retail A[/TD]
[TD]Cat5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Retail B[/TD]
[TD]Cat1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Retail B[/TD]
[TD]Cat2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Retail B[/TD]
[TD]Cat3[/TD]
[TD][/TD]
[TD]

50​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Retail B[/TD]
[TD]Cat4[/TD]
[TD][/TD]
[TD]

40​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Retail B[/TD]
[TD]Cat5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Retail C[/TD]
[TD]Cat1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Retail C[/TD]
[TD]Cat2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Retail C[/TD]
[TD]Cat3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Retail C[/TD]
[TD]Cat4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.