When we work in excel sheets, we are required to find a matching value in a row and return the Column name. For example, we need to calculate the lowest priced store from the price table of multiple stores for multiple products. Another example can be found at the primary reason for exits from the exit feedback form analysis. In these cases, even multi-condition vlookup doesn’t help. We will take the first example and find out how to solve this using the Excel formulas.
Recommended Reading:
Conditional VLOOKUP with MATCH In EXCEL
Example:
In the above table we have to return the company name which has lowest price for that product. For this we need to find the column which has the lowest price. So we need to use min(Range) excel worksheet formula. This will give us which is the lowest value. Now we need to find the relative position of the value. For this, match worksheet formula is useful. Now have to get the column name of that position. For this we need to use index function. The final formula will become like below.
=INDEX($B$1:$E$1,MATCH(MIN(B2:E2),B2:E2,0)) which returns Company A.
How it works?
MIN(B2:E2) returns 16999.
MATCH(MIN(B2:E2),B2:E2,0) returns 1
INDEX($B$1:$E$1,MATCH(MIN(B2:E2),B2:E2,0)) returns Company A as column 1 in range B1 to E1 is B1.
Note: The column range for index and match should be the same. Only the row numbers can be different. As match function returns relative position data, the column range should be same for Index function also.
You can tweak this function according to your requirements. You might required to find the column name when you know the exact value in the row. In that case, you need to replace min function with required value.
