INDEX & MATCH for multiple contiguous columns & row (i.e. a range) ?

[jxb]

INDEX & MATCH for multiple contiguous columns & row (i.e. a range) ?

I cannot remember the exact syntax to find a value in a range of multiple columns and rows (a “table” and returns a value in a specific column (in the same row). I used in the past but forgotten !

In the example below

Max value in range B2:D4

Returns the value in the same row in column A

Is it also possible to return the column the max value is ?


Thanks

John

 

[KRice]

One approach...

MrExcel_20220805.xlsx
	A	B	C	D	E	F	G
1	ID	v1	v2	v3		Max at:
2	61	100	2	3		row index	3
3	81	41	11	6		column index	1
4	71	101	1	9		ID at Max val	71
Sheet4

Cell Formulas
Range	Formula
G2	G2	=MAX(IF($B$2:$D$4=MAX($B$2:$D$4),ROW($2:$4)-ROW($2:$2)+1))
G3	G3	=MAX(IF($B$2:$D$4=MAX($B$2:$D$4),COLUMN($B:$D)-COLUMN($B:$B)+1))
G4	G4	=INDEX($A$2:$A$4,G2)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1		A	B	C
2	61	100	2	3
3	81	6	11	6
4	71	101	1	9
5
6
7	Row	71
8	Col	A
Main

Cell Formulas
Range	Formula
B7	B7	=INDEX(A2:A4,AGGREGATE(15,6,(ROW(A2:A4)-ROW(A2)+1)/(B2:D4=MAX(B2:D4)),1))
B8	B8	=INDEX(B1:D1,MATCH(MAX(B2:D4),INDEX(B2:D4,MATCH(B7,A2:A4,0),),0))

 

[CA_Punit]

You can try this for example:

=INDEX(A2:A4,AGGREGATE(14,6,MMULT(IFERROR(SEARCH(MAX(B2:D4),B2:D4)^0,0),SEQUENCE(3)^0)*SEQUENCE(3),1),1)

 

[jxb]

One approach...

MrExcel_20220805.xlsx
	A	B	C	D	E	F	G
1	ID	v1	v2	v3		Max at:
2	61	100	2	3		row index	3
3	81	41	11	6		column index	1
4	71	101	1	9		ID at Max val	71
Sheet4

Cell Formulas
Range	Formula
G2	G2	=MAX(IF($B$2:$D$4=MAX($B$2:$D$4),ROW($2:$4)-ROW($2:$2)+1))
G3	G3	=MAX(IF($B$2:$D$4=MAX($B$2:$D$4),COLUMN($B:$D)-COLUMN($B:$B)+1))
G4	G4	=INDEX($A$2:$A$4,G2)

in reply to KRice suggestion: with Ctrl+ Shift + Enter I take it !

 

[CA_Punit]

You can try this for example:

=INDEX(A2:A4,AGGREGATE(14,6,MMULT(IFERROR(SEARCH(MAX(B2:D4),B2:D4)^0,0),SEQUENCE(3)^0)*SEQUENCE(3),1),1)

I am so sorry i didn't notice your excel version. You might ignore

 

[Fluff]

The formula in G2 & G3 will need Ctrl Shift Enter, although the formulae I suggested don't need it.

 

[jxb]

I am so sorry i didn't notice your excel version. You might ignore

Thanks. Indeed I get a #NAME?

 

[jxb]

The formula in G2 & G3 will need Ctrl Shift Enter, although the formulae I suggested don't need it.

Thanks a lot. These are the ones I am looking at right now. As "straight" formulas

 

[Fluff]

Glad we could help & thanks for the feedback.