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.

 

[aprill]

Peter_SSs, AGGREGATE absolutely worked. Thanks. I definitely appreciate the help. Would you help me understand why MATCH didn't work?

Thanks
April

 

[Peter_SSs]

@Peter_SSs, AGGREGATE absolutely worked. Thanks.

You're welcome.

can anyone tell me why MATCH would not work?

MATCH returns the position of an item in an array but it can only return a single value, You were looking for a value in a two dimensional array. To locate that item you would need to return a row and a column, that is, two values. A single return cannot do that. So MATCH needs to be looking in a single column or a single row.

 

[aprill]

@Peter_SSs,

Thank you so much for that explanation. I really appreciate you taking the time to help me.

April

 

[Peter_SSs]

No problem. Glad to help.

 

[aprill]

No problem. Glad to help.

I am trying to point the aggregate function at a different spreadsheet but I am striking out.

If I use
=AGGREGATE(15,6,ROW('OBS-H'!C6:H2000)/('OBS-H'!C6:H2000=D4),1)
It works great. However, I would like to point to a cell (E4) that contains the name of the worksheet

The formula in E4 is =LOOKUP(9.99999999999999E+307,1/COUNTIF(INDIRECT("'"&SheetList&"'!C6:H2000"),D4),SheetList)
with Sheetlist being the named range of the worksheets. This function returns the name of the spreadsheet that contains the max value.

But when I point to the cell, the formula returns #NUM!
=AGGREGATE(15,6,ROW(INDIRECT("'"&E4&"'!C6:H2000"))/INDIRECT("'"&E4&"'!C6:H2000")=D4,1)

I also tried putting the INDIRECT in front of AGGREGATE and that didn't work either.

 

[Peter_SSs]

If I use
=AGGREGATE(15,6,ROW('OBS-H'!C6:H2000)/('OBS-H'!C6:H2000=D4),1)
It works great.

But when I point to the cell, the formula returns #NUM!
=AGGREGATE(15,6,ROW(INDIRECT("'"&E4&"'!C6:H2000"))/INDIRECT("'"&E4&"'!C6:H2000")=D4,1)

The second formula is simply missing the highlighted parentheses from the first one.

=AGGREGATE(15,6,ROW(INDIRECT("'"&E4&"'!C6:H2000"))/(INDIRECT("'"&E4&"'!C6:H2000")=D4),1)

 

[aprill]

The second formula is simply missing the highlighted parentheses from the first one.

=AGGREGATE(15,6,ROW(INDIRECT("'"&E4&"'!C6:H2000"))/(INDIRECT("'"&E4&"'!C6:H2000")=D4),1)

Thanks for the catch. I again appreciate your help.

 

[Peter_SSs]

Cheers.