Find Minimum Value and Return Value from Another Column [Google Sheets]

0
83
Find Minimum Value and Return Value from Another Column

As you know, the function MIN can return minimum value in a numeric data set. But the question is can we use MIN to return adjacent cell values other than minimum value? Didn’t get? I am talking about a vertical lookup where the search key is the smallest value in the list. If you have still doubt, just refer the screenshot above. Let’s see how to find minimum value and return value from another column in Google Sheets.

Lookup or Find Minimum Value and Return Value from Another Column

Sample Data and MIN formula:

The following sample data contains name of few persons and their age.

vertical lookup based on MIN value in Google Sheets

The MIN formula in Cell C3 returns the minimum value or smallest value in the numeric column B and it’s 16. The number 16 is the age of the person “Erinn Cady” and the name is in Column A. Can we lookup column B for minimum age and return the name from Column A. The answer is Yes.

You may think that you can use Vlookup for this purpose. But the problem here is, Vlookup looks the first column for a match and returns the corresponding value from another column. So a kind of Reverse lookup we want here.

Our lookup value is in Column B and we want to return the result from Column A. Further to deepen the problem, we don’t have the search key or minimum value in hand to use directly in the lookup. So the solution is an Index, Match, MIN combo formula. With this killer combination of functions, we can find minimum value in Google Sheets and return values from another column. Here we go.

Use of MIN Function in Vertical Lookup

The below is the above hyped combination formula for the kind of lookup we are talking about.

=index(A3:B12,match(min(B3:B12),B3:B12,FALSE),1)

Formula Part Explanation:

Let me explain this formula with the syntax of Index function. Please refer our function guide to learn the functions used in the combo here individually.

Syntax:

INDEX(reference, [row], [column])

With colour pattern, I have tried to compare the syntax of Index with our combo formula. The “row” part (in blue colour) in the Index is the key in this combo formula. If we have the row number of the smallest value, we can easily return the name of the person by putting the number in Index as row number. So I’ve used Match and Min combination to return the row number as below.

match(min(B3:B12),B3:B12,FALSE)

See the Match syntax now.

MATCH(search_key, range, [search_type])

The Roll of MIN in the combo:

The MIN function in the combo formula returns the smallest value in column B.

The Roll of Match in the combo:

The Match formula uses this number as “search_key” to return the relative position of the number which is the row number.

This way you can find minimum value and return value from another column in Google Sheets. That’s all.

LEAVE A REPLY

Please enter your comment!
Please enter your name here