Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles LOOKUP Lookup lowest value

Lookup lowest value

While working with Excel, we are able to retrieve a value from a dataset through lookup based on a lowest value by using the INDEX, MATCH and MIN functions.  This step by step tutorial will assist all levels of Excel users to look up a value based on a lowest value in a data set.  

Figure 1. Final result: Lookup lowest value

Final formula: =INDEX(B3:B7,MATCH(MIN(C3:C7),C3:C7,0))

Syntax of the INDEX function

The INDEX function returns a value as specified from within a range.

=INDEX(array, row_num, column_num)

The parameters are:

  • array – a range of cells where we want to retrieve some data
  • row_num – the row in the array from which we want to retrieve data
  • column_num – the column in the array from which we want to retrieve data; if the array has only one column, column_num can be omitted

Syntax of the MATCH function

The MATCH function returns the position of a value in a range.

=MATCH(lookup_value, lookup_array, [match_type])

The parameters are:

  • lookup_value – a value which we want to find in the lookup_array
  • lookup_array – the range of cells containing the value we want to match
  • [match_type] optional; the type of match; if omitted, the default value is 1; We use 0 to find an exact match

Syntax of the MIN function

The MIN function returns the lowest value in a data set.  

=MIN(number1, [number2], ...)  

The parameters are:

  • number1, number2, … – the numbers for which we want to find the lowest value; only number1 is required; succeeding numbers are optional
  • The arguments could be numbers, array or reference to cells containing numbers

Setting up Our Data

Our table contains a list of Class (column B) and number of Students (column C). In cell E3, we we want to determine the class with the lowest number of students.  

Figure 2. Sample data to lookup lowest value

Lookup Lowest Value

In order to determine the specific class with the lowest number of students, we follow these steps:

Step 1.  Select cell E3

Step 2.  Enter the formula: =INDEX(B3:B7,MATCH(MIN(C3:C7),C3:C7,0))

Step 3: Press ENTER

Figure 3. Using INDEX, MATCH and MIN to lookup the class with lowest students

Our array for the INDEX function is the range B3:B7 which contains our data for “Class”.  The row number is determined through the MATCH and MIN functions.  

First, the MIN determines the lowest value for number of students, which is 25.  The MATCH function then returns the position of “25” in the range C3:C7, which is 4.  Hence, the row number is 4.  The column number is 0 since there is only one column in our array B3:B7.   

As a result, our formula returns “Biology” in cell E3, which is the class with the lowest number of students, and positioned in row 4 of the range B3:B7.  

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
index and match with duplicates. I need to use another column as a reference, so my return value has two match the value of two things for it to return
Solved by E. H. in 60 mins
I need to consolidate a list of names based on lowest number. Each name is associated with an account and a date. For each account I need only the lowest date to show up. I am using a pivot table.
Solved by C. H. in 60 mins
Hello, I have a big spreadsheet in which I need to know how many patients came in each month based on provider. I am using the following formula but I still get the N/A error. =INDEX('No Show Appts Data'!D:E, MATCH(1, 'No Show Appts Data'!D:D='No Shows Data'!M2)*('No Shows Data'!E:E='No Shows Data'!N1),0)) No Show Appts Data is the name of the sheet where Column D is Month Year of date in question and Column E is the provider. No Shows Data is the sheet where I am making all the formula calculation where Column M is Month and Year and Column N is the provider in question and therefore M2 is the month in question and N1 the provider in question. How do I fix this error? Thanks
Solved by F. H. in 40 mins
I need to find an INDEX function that will convert the Call Day (a number 1-7) to the actual weekday found in row 1 of the DayofWeek named range. I don't know if I'm supposed to use the MATCH function as well or not.
Solved by D. D. in 11 mins
I have two columns with names and a third one with email addresses. For every name that matches, I need to copy the email address to an empty column next to the matching name. .
Solved by E. U. in 15 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc