Summary

To lookup information associated with the lowest value in table, you can use a formula based on INDEX, MATCH, and MIN functions.

In the example shown, a formula is used to identify the name of the contractor with the lowest bid. The formula in F6 is:

=INDEX(B5:B9,MATCH(MIN(C5:C9),C5:C9,0))

Generic formula

=INDEX(range,MATCH(MIN(vals),vals,0))

Explanation 

Working from the inside out, the MIN function is used to find the lowest bid in the range C5:C9:

MIN(C5:C9) // returns 99500

The result, 99500, is fed into the MATCH function as the lookup value:

MATCH(99500,C5:C9,0) // returns 4

Match then returns the location of this value in the range, 4, which goes into INDEX as the row number along with B5:B9 as the array:

=INDEX(B5:B9, 4) // returns Cymbal

The INDEX function then returns the value at that position: Cymbal.

Related formulas

Excel formula: INDEX and MATCH case-sensitive

INDEX and MATCH case-sensitive

In this example, the goal is to perform a case-sensitive lookup on the first name in column B, based on a lookup value in cell F6. By default, Excel is not case-sensitive and this applies to standard lookup formulas like VLOOKUP , XLOOKUP , and INDEX and MATCH . These formulas will simply return...
Excel formula: INDEX and MATCH exact match

INDEX and MATCH exact match

In this example, the goal is to look up various information about a random group of popular movies from the 1990s. The information to retrieve includes the year released, the rank against the other movies in the list, and worldwide gross sales. To retrieve this information, we are using an INDEX...
Excel formula: Minimum if multiple criteria

Minimum if multiple criteria

In this example, the goal is to get the minimum value for a given group above a specific temperature. In other words, we want the min value after applying multiple criteria. The easiest way to solve this problem is with the MINIFS function. However, if you need more flexibility (for example, you...

Related functions

Excel INDEX function

INDEX Function

The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers....
Excel MATCH function

MATCH Function

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined with the...
Excel MIN function

MIN Function

The Excel MIN function returns the smallest numeric value in the data provided. The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.

Related videos

How to look things up with INDEX

What does the INDEX function do? Unlike the MATCH function , which gets the position of an item in a list or a table, INDEX assumes you already know the position, and it gets the value of the item at that position. Let's take a look. INDEX is a powerful and flexible function that can be used for...

How to use the MATCH Function for exact matches

The MATCH function finds the relative position of an item in a list. MATCH can find exact matches or approximate matches. In this video, we'll look at how to use MATCH to find an exact match. The MATCH function takes three arguments: the lookup_value, which is the value you're looking up, the...

How to do a two-way lookup with INDEX and MATCH

In this video we'll look at how to set up a classic two-way lookup using INDEX and MATCH . Here we have a list of salespeople with monthly sales figures. What we want to do is add a formula in Q6 that looks up and retrieves a sales number based on the name and month above. To do this, we'll use the...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions

Videos