Find matching value in a row and return column name in Excel

When we work in excel sheets, we are required to find matching value in a row and return the Column name. For example, we need to calculate lowest priced store from the price table of multiple stores for multiple products. Another example can be finding the primary reason for exits from the exit feedback form analysis. In these cases, even multi condition vlookup doesn’t helps. We will take the first example and find out how to solve this using the Excel formulas.
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.

Meet the Author

Hi, I am Aniruddha, an avid blogger since 2008 who likes writing about new gadgets, smartphones and excel. You can find them at www.tech2touch.com. You can also follow me on Twitter @tech2touch or my Facebook page tech2touch. You can subscribe to my YouTube channel tech2touch. Enjoy reading and watching!!!

• Vishwaroop Feb 20, 2015, 5:04 am

Very useful in real-time applications. Want to know if the same can be tweaked for row based values instead of columns.

• Katherine Mar 5, 2016, 8:42 pm

This works great for what I need – almost! What about duplicate low values? If I had two companies that both quoted a low of \$450, how can I record that? Only the first found lowest returns the company name. Thanks

• Aniruddh Mar 8, 2016, 12:20 am

The simplest solution is to sort the lookup range in ascending oder and then do the lookup. Lookup fetches the first value.

• zety May 26, 2018, 2:34 am

How to get column name if the column is like this ; B2,B4,B6. And how I copy this formula to next row below? Thanks.

• Aniruddh May 26, 2018, 7:06 am

You need to make cells absolute reference. Are you looking to return the column name based on certain value?

• Toby Jul 3, 2018, 6:11 am

It works and helpful. Thank you