This post will guide you how to find the largest value in a row and return column header in Excel. How to retrieve the column header of the largest value in a row with a formula in Excel. How to return the column title of the max value in the row in Excel.
Return the Column Header of the Largest Value in a Row
Assuming that you have a list of data in range A1:D4, in which contain sales data for each month. And you want to find the highest value for each product and return the Month column header in your worksheet. How to do it. You can use an Excel formula based on the INDEX function, the MATCH function and the MAX function. Like this:
You need to type this formula into cell E2 and press Enter key on your keyboard, and then copying this formula from cell E2 to range E3:E4, you would notice that you will get the column header for in every row.
Let’s see how this formula works:
The INDEX function will return a reference of the cell at the intersection of a specific row and column in range B1:D1.
The MAX function will return the maximum number from a given range B2:D2. And pass the returned result into the MATCH function as its argument.
The MATCH function will return the relative position of the maximum value in the given range.
Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel MATCH function
The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel MAX function
The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…