Index with multiple matches (verticle & horizontal)

[cshetty]

Dear Experts
Please help me to tune this formula.

=INDEX($F$4:$M$10,MATCH(1,($E15=$D$4:$D$10)*(F$14=$E$4:$E$10),0),MATCH(1,($D15=$F$3:$M$3),0))

Data layout image attached.

Thank you in advance.

 

[Peter_SSs]

@MARZIOTULLIO
Answering a question asked of you with a video that is over an hour long is not appropriate.


@cshetty

the syntax would be Index(range, row, col). Why we use row number as ZERO when we use Index function inside other function ?

It is not related to the INDEX being inside another function. When 0 is used for the row number in INDEX then INDEX will return all rows. It is covered by the 3rd bullet point in the Remarks section of the INDEX Help

Here is a simple example. The index function returns all rows (because of the zero) of the second column of the target range.

24 11 22.xlsm
	A	B	C	D
1	a	b		b
2	c	d		d
3	e	f		f
4	g	h		h
INDEX sample

Cell Formulas
Range	Formula
D1:D4	D1	=INDEX(A1:B4,0,2)
Dynamic array formulas.

 

[cshetty]

@MARZIOTULLIO
Answering a question asked of you with a video that is over an hour long is not appropriate.


@cshetty

It is not related to the INDEX being inside another function. When 0 is used for the row number in INDEX then INDEX will return all rows. It is covered by the 3rd bullet point in the Remarks section of the INDEX Help

Here is a simple example. The index function returns all rows (because of the zero) of the second column of the target range.

24 11 22.xlsm
	A	B	C	D
1	a	b		b
2	c	d		d
3	e	f		f
4	g	h		h
INDEX sample

Cell Formulas
Range	Formula
D1:D4	D1	=INDEX(A1:B4,0,2)
Dynamic array formulas.

Thank you Peter for this crisp explanation. I got it now.

 

[Peter_SSs]

Cheers. You’re welcome.