Even if I say Min in Vlookup in Google Sheets I may not use the Min function in all the examples. If I want to find a Min value and return a corresponding value, of course, I will use the Min, Mina or the Small function. But if I want to find min values of each group, then I will use a different method as in that case the search key won’t be the min value.
I hope you are going to enjoy this Min in Vlookup in Google Sheets tutorial. I am going to share with you small tips and tricks related to min value use in Vlookup.
How to Use Min in Vlookup in Vlookup in Google Sheets
The Vlookup has the following arguments in it.
VLOOKUP(search_key, range, index, [is_sorted])In this, we want to use the search_key as the min value.
Vlookup will only search down the first column in a range. But that doesn’t mean the min column, from which we want to find the min and use as the search key, must be the first column. It’s quite contradicting, right?
Let me start with the first column Min Vlookup in Google Sheets.
First Column Min in Vlookup in Google Sheets
I have sample data of the total budget of a few campaigns in column A and the campaign names in column B. See how I am going to find the min budget and return the campaign name.
Vlookup Formula # 1: Min
=vlookup(min(A2:A),A2:B,2,0)
The Min function returns the min value as the search_key in Vlookup. Here the min column is the first column and so there is no issue in Vlookup.
I will come to the Vlookup min in a different column later. We should pay our attention to a few other things first.
Sometimes you may want to replace the function Min with Mina. Do you know when?
Mina in Vlookup
Assume cell A6 contains the string ‘Nil’ instead of the number 600. You may want to treat this string as 0 and return “Campaign 5” as the Vlookup result. Is that possible using the Mina function in Vlookup as below?
=vlookup(MINA(A2:A),A2:B,2,0)Unfortunately “No”. Of course, the Mina function will return 0 as the min value and Vlookup will use this value as the search_key. Since 0 is not in the first column in the Vlookup range, the formula will return N/A error.
To sort out this issue, you should convert the text string in column A to 0 in Vlookup range also using the function N. Since the function N is not an array function, use the ArrayFormula function too.
So, the formula using Vlookup, Mina, and N function combo will be as below.
Vlookup Formula # 2: Mina
=ArrayFormula(vlookup(MINA(A2:A),{n(A2:A),B2:B},2,0))
Note: This Mina use won’t be helpful always. If there are multiple strings in the Mina column, the formula would return the value corresponding to the first string.
Small in Vlookup
If you want to Vlookup second as well as third (or any other) Min value in Google Sheets, use Small function instead of the Min function.
Here I am not including any screenshot. Straightaway to two formulas.
Vlookup Formula # 3: Small
=vlookup(small(A2:A,2),A2:B,2,0)The result would be “Campaign 7” because the second smallest value in the first column is 422. Here is one more formula.
=vlookup(small(A2:A,3),A2:B,2,0)Result: “Campaign 2”
Vlookup Min and Return Multiple Values from the Found Row
In the above examples, we have used the Vlookup formulas to return value from the “Campaign” column B. When there are values in column C also and you want to return values from both column B and C, follow the below method.
Replace the “Vlookup Formula # 1” with the below one.
Vlookup Formula # 4: Min Array
=ArrayFormula(vlookup(min(A2:A),A2:C,{2,3},0))To search across the first column for Min value and return values form the corresponding row, use the column numbers (index) within curly braces.
Min Other than in the First Column in Vlookup in Google Sheets
You are going to learn the use of Min in Leftward or Reverse Vlookup in Google Sheets.
When using Min in Vlookup in Google Sheets, you may normally want to use reverse Vlookup. Because in most of the datasets, the Min to be applied won’t be in the first column.
Actually, I have detailed this topic in an earlier tutorial. That time I have used Index and Match instead of Vlookup – Find Minimum Value and Return Value from Another Column.
To use Min function in reverse Vlookup in Google Sheets, you must shuffle the columns within Vlookup. Because Vlookup can ONLY search down the first column. We can’t change that.
What we can do is move the column to search down to the first. We can achieve this by shuffling the dataset (Vlookup range) as below.
Example to Shuffling Columns in Google Sheets Vlookup
Here is one example of how to shuffle columns in Google Sheets.
={B1:B,A1:A}
That means instead of A2:B as the range, use the shuffled range {B2:B,A2:A} in Min in reverse Vlookup in Google Sheets.
Vlookup Formula # 5: Min Reverse
=Vlookup(min(B2:B),{B2:B,A2:A},2,0)Vlookup to Return Min Value From Each Group
In the following example, the Vlookup search_key is not the output of Min, Mina or Small functions. We are finding the Min values in each group. See this example.
I want to find the Min value of “Campaign 1” in column 3, which is 25. So the search_key in Vlookup will be “Campaign 1”.
For this, you must sort the data (column 3) in ascending order within Vlookup. There is one more method which is my favorite. I’ll come to that later.
Method 1: Move Min Row to the Top and Vlookup
Vlookup Formula # 6: Min in Group
=vlookup("Campaign 1",sort(A2:D,3,TRUE),3,0)This formula will return 25 which is the min value of the group “Campaign 1”. What will happen if we do not sort the data?
Vlookup will return the value 225. This is because, in the case of multiple matching values, Vlookup will return the content of the cell corresponding to the first value found.
It is applicable if the Vlookup optional argument is_sorted has specified in the formula as 0 (false). See the 0 at the last part of the Vlookup formula above.
To return the Min value of each group, use the formula as given below.
Vlookup Formula # 7: Min in Each Group
=ArrayFormula(vlookup({"Campaign 1";"Campaign 2"},sort(A2:D,3,TRUE),3,0))or use this flexible formula that includes Unique.
=iferror(ArrayFormula(vlookup(unique(A2:A),sort(A2:D,3,TRUE),3,0)))Method 2: Remove Rows Other Than Min
Here you can skip using Vlookup. The below formula will return the Min value rows from each group.
Formula # 8: Min Rows
=sortn(sort(A2:D,3,true),9^9,2,1,true)Formula Explanation:
The Sort function sorts the data in descending order. The Sortn only returns the first row from each group from this sorted data.
Hope I have covered everything that you want to know about Min in Vlookup in Google Sheets. Any doubt, please post in the comments.
