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

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

Excel Lookup

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.

7 comments… add one
  • 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

  • Afzal Siddiqui Aug 18, 2018, 9:49 am

    Hi, Anirudha,
    Thanks for this excel trick, I’m sure it will help me on my excel database I’m maintaining right now.
    Keep up doing

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.