MATCH()

[garyfritz]

I have some very strange MATCH() behavior. I've narrowed it down to a very simple case. In the attached image, columns A-D are all just constants. F1 is =LARGE(A1:D1,1) and G1 is =MATCH(LARGE(A1:D1,1),A1:D1), and those formulas are copied down the columns.

You can see that the LARGE() in col F is working just fine. For example, F1 says the largest value is 1.0263. But G1, which contains =MATCH(LARGE(A1:D1,1),A1:D1), says column 4 matches that largest value!? G2:G6 are correct, then G7:G9 are wonky again, and so on. In all those cases, column 1 is actually the largest value.

It also sometimes selects the wrong column if I ask for the 2nd-largest value. If I ask for 3rd-largest I get #N/A in rows 1 and 7-9 (same as the erroneous rows before). If I ask for 4th largest, all cells are #N/A except row 11, which correctly says column 4 is 4th largest.

Am I doing something obviously wrong here ??

 

[kweaver]

change

=MATCH(LARGE(A1:D1,1),A1:D1)

to

=MATCH(LARGE(A1:D1,1),A1:D1,0)

 

[garyfritz]

Ahhh. I didn't notice that the default requires the array to be in sorted order. Match type 0 works like a charm. Thanks!

 

[Rahul1987]

How about?

Book1
	A	B	C	D	E	F	G
1	Position 1	Position 2	Position 3	Position 4		Highest amt	Rank
2	1	105	100	98		105	2
3	988	5	68	58		988	1
4	4	8987	98	57		8987	2
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=LARGE(A2:D2,1)
G2:G4	G2	=MATCH(LARGE(A2:D2,COLUMN(A2:D2)-COLUMN($A2)+1),A2:D2,0)

 

[garyfritz]

How about?

It looks like you're using the same MATCH logic, but you just added code to calculate the Nth largest based on the column # ? That works great, but I really don't need that functionality. Just fixing the MATCH was all I needed. Thanks tho!

 

[Rahul1987]

No problem, thanks for your feedback.