MATCH returns #N/A for a range that contains the value

[aprill]

I use =MAX(C6:H900)to find the max value in a range. I put that value in K29. Then I try to use =MATCH(K29,C6:H900) to find that value in the range to return the row. MATCH returns #N/A. I checked the type of both K29 and the range and they are both v. If I use =MATCH(K29,K29), a 1 is returned. So MATCH does seem to find the value but as soon as I expand the range, MATCH returns #N/A. I am using Excel Version 16.40. I could really use some help.

 

[kweaver]

This ARRAY formula (CTRL+Shift+Enter) will find the cell address which you could modify to just grab the row number:

=ADDRESS(SUM(IF(C6:H900=MAX(C6:H900),ROW(C6:H900))),SUM(IF(C6:H900=MAX(C6:H900),COLUMN(C6:H900))))

If the cell address from above is in K8, this formula will give you the row.

=VALUE(RIGHT(K8,LEN(K8) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},K8&"0123456789")) +1))

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is it possible with your data that the max value can occur more than once in the range? If so, what result(s) do you want returned & what layout?

One of these will give you the first row depending on whether you want the actual row number or the relative row number in the table.

20 09 05.xlsm
	C	D	E	F	G	H	I	J	K
1
2
3
4
5
6	484	884	563	534	194	392
7	685	165	371	118	196	420
8	341	402	492	619	97	245
9	569	714	922	561	858	891			965
10	722	938	270	783	135	502			8
11	69	811	233	830	441	923			13
12	271	217	599	946	617	292
13	467	344	482	717	965	726
14	258	581	327	100	736	169
15	771	159	662	394	651	199
16	945	256	267	295	547	840
17	229	852	93	614	187	628
18	275	759	470	127	208	416
19	503	965	467	327	769	788
20	576	614	318	556	332	854
21	940	273	598	72	615	8
22	544	529	666	304	719	794
23	902	539	875	627	55	464
24	358	287	34	651	480	844
Max_Row

Cell Formulas
Range	Formula
K9	K9	=MAX(C6:H900)
K10	K10	=AGGREGATE(15,6,ROW(C6:H25)/(C6:H25=K9),1)-ROW(C6)+1
K11	K11	=AGGREGATE(15,6,ROW(C6:H25)/(C6:H25=K9),1)

If the cell address from above is in K8, this formula will give you the row.

=VALUE(RIGHT(K8,LEN(K8) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},K8&"0123456789")) +1))

Much simpler would be
=REPLACE(K8,1,FIND("$",K8,2),"")+0

 

[Tom.Jones]

@Peter_SSs ,

It's typo in your formula...
Your formula in K11 is:
=AGGREGATE(15,6,ROW(C6:H25)/(C6:H25=K9),1)

and should be:
=AGGREGATE(15,6,ROW(C6:H25)/(C6:H25=K9),1) + 1

 

[Peter_SSs]

@Peter_SSs ,

It's typo in your formula...
Your formula in K11 is:
=AGGREGATE(15,6,ROW(C6:H25)/(C6:H25=K9),1)

and should be:
=AGGREGATE(15,6,ROW(C6:H25)/(C6:H25=K9),1) + 1

Why? With my sample data that would return 14 when the maximum value is in row 13.


I did have errors though as I tested on a smaller range and forgot to reinstate the OP's range. Corrected here.

20 09 05.xlsm
	C	D	E	F	G	H	I	J	K
1
2
3
4
5
6	484	884	563	534	194	392
7	685	165	371	118	196	420
8	341	402	492	619	97	245
9	569	714	922	561	858	891			965
10	722	938	270	783	135	502			8
11	69	811	233	830	441	923			13
12	271	217	599	946	617	292
13	467	344	482	717	965	726
14	258	581	327	100	736	169
15	771	159	662	394	651	199
16	945	256	267	295	547	840
17	229	852	93	614	187	628
18	275	759	470	127	208	416
19	503	965	467	327	769	788
20	576	614	318	556	332	854
21	940	273	598	72	615	8
22	544	529	666	304	719	794
23	902	539	875	627	55	464
24	358	287	34	651	480	844
Max_Row

Cell Formulas
Range	Formula
K9	K9	=MAX(C6:H900)
K10	K10	=AGGREGATE(15,6,ROW(C6:H900)/(C6:H900=K9),1)-ROW(C6)+1
K11	K11	=AGGREGATE(15,6,ROW(C6:H900)/(C6:H900=K9),1)

 

[Tom.Jones]

Peter,

Why? With my sample data that would return 14 when the maximum value is in row 13.

Maybe I understand wrong...

Your formula - =AGGREGATE(15,6,ROW(C6:H900)/(C6:H900=K9),1) return 13 and the second 965 is in row 14

Row 1	484​	884​	563​	534​	194​	392​
Row 2	685​	165​	371​	118​	196​	420​
Row 3	341​	402​	492​	619​	97​	245​
Row 4	569​	714​	922​	561​	858​	891​
Row 5	722​	938​	270​	783​	135​	502​
Row 6	69​	811​	233​	830​	441​	923​
Row 7	271​	217​	599​	946​	617​	292​
Row 8	467​	344​	482​	717​	965​	726​
Row 9	258​	581​	327​	100​	736​	169​
Row 10	771​	159​	662​	394​	651​	199​
Row 11	945​	256​	267​	295​	547​	840​
Row 12	229​	852​	93​	614​	187​	628​
Row 13	275​	759​	470​	127​	208​	416​
Row 14	503​	965​	467​	327​	769​	788​
Row 15	576​	614​	318​	556​	332​	854​
Row 16	940​	273​	598​	72​	615​	8​
Row 17	544​	529​	666​	304​	719​	794​
Row 18	902​	539​	875​	627​	55​	464​
Row 19	358​	287​	34​	651​	480​	844​

 

[jasonb75]

Maybe I understand wrong...

Your formula - =AGGREGATE(15,6,ROW(C6:H900)/(C6:H900=K9),1) return 13 and the second 965 is in row 14

Both of Peter's formulas are returning the first instance of 965. The first shows the position of the row in the table, the second shows the position in the sheet.

To find the position of the second 965 you would need to change the [k] value of AGGREGATE to 2. Using the same table as Peter in post 5.

Book1
	K
9	965
10	14
11	19
Sheet1

Cell Formulas
Range	Formula
K9	K9	=MAX(C6:H900)
K10	K10	=AGGREGATE(15,6,ROW(C6:H900)/(C6:H900=K9),2)-ROW(C6)+1
K11	K11	=AGGREGATE(15,6,ROW(C6:H900)/(C6:H900=K9),2)

 

[Tom.Jones]

Now is clear!
Thanks jasonb75.

 

[Peter_SSs]

Both of Peter's formulas are returning the first instance of 965. The first shows the position of the row in the table, the second shows the position in the sheet.

Exactly, as I pointed out in my post.

One of these will give you the first row depending on whether you want the actual row number or the relative row number in the table.

 

[aprill]

Thanks for your help. I am going to try aggregate but can anyone tell me why MATCH would not work?

April