Excel find max value in a column if column number is given

0
votes

I'm trying to find maximum value in a given column using excel formulae if column number (like 2, 3, 4) has been specified in cell A2.

For example, if column number in A2 is 2, then the formula should give 220 (max. value under Product 2 column) as he answer.

The data is as follows:

Product1.    Product 2.    Product 3      
200.              150.              180
160.              220.               200
210.              190.               230
1
excel
Used D2 =substitute(address(1, A2, 4), 1, " ") to get column letter and tried using it in = max(column(D2)). Thing is, column(D2) returns 4, which is column D – AR06

1 Answers

1
votes

You can use Max with Index. Index takes A2 value as column argument and generates an array of the values within that column for the specified range. Max then finds the max value in that array.

=MAX(INDEX(C3:E5,,A2))

data