Rubin Shrestha
Zend certified PHP/Magento developer
Rubin Shrestha
Blog

Excel: Get max value in specific row with row number retrieved by a formula

by |Published

I should like to get the max value in a specified row in another worksheet, where the row number is retrieved by a formula.
I can call up the desired row number from the sheet called “Kur” by this formula: =MATCH(B2,Kur!$A$1:$A$35,0).
That specific formula gives me the number 23 but this number of course changes when B2 is changed. So the formula =MATCH(B2,Kur!$A$1:$A$35,0) gives me the row number I want. =MAX(Kur!23:23) works as expected and gives me the largest value in row 23 in the sheet called “Kur”. But I cannot figure out how to combine the two and to use the result from my formula MATCH(B2,Kur!$A$1:$A$35,0) as a parameter for the MAX function. I have tried:
=MAX(Kur!MATCH(B2,Kur!$A$1:$A$35,0):MATCH(B2,Kur!$A$1:$A$35,0))
=MAX(Kur!MATCH(B2,Kur!$A$1:$A$35,0)&”:”&MATCH(B2,Kur!$A$1:$A$35,0))
=MAX(Kur!&MATCH(B2,Kur!$A$1:$A$35,0)&”:”&MATCH(B2,Kur!$A$1:$A$35,0))
But with no success. Help would be very much appreciated.

You may also like