While working with Excel it becomes necessary sometimes to find the lowest value in a dataset easily and quickly for calculations. If you are looking for some of the easiest ways to find minimum value with VLOOKUP in Excel, then you will find this article worth it. By using the following methods you will be able to do this job effectively.
How to Find Minimum Value with VLOOKUP in Excel: 6 Effective Ways
I will explain the methods to find minimum value with VLOOKUP in Excel using the following data table. In the table, there are marks distribution of some students in a college.
Using the following methods, we can find the minimum value easily. For doing these calculations I am using Microsoft Excel 365 version, you can use any other version according to your convenience.
Method 1: Minimum Value with VLOOKUP Function
If you want to know the minimum mark and grade achieved for the minimum mark then you can use the VLOOKUP function and the MIN function.
1.1. Find Minimum Value
You can just apply those two functions to find minimum value from a range.
Steps:
➤ Select the output Cell C12
➤ Type the following formula
=VLOOKUP(MIN($C$5:$C$10),$C$5:$D$10,1,FALSE)
 💡 Formula Breakdown
MIN($C$5:$C$10) will return the minimum value in the range $C$5:$C$10
Then it will be the lookup value for the VLOOKUP function, $C$5:$D$10 is the table array, 1 is the column index number which is the Grade column here and FALSE is for Exact Match
➤ Press ENTER
Then you will find the Minimum Mark which is 65.
Read More: How to Find Minimum Value in Excel
1.2. Return Adjacent Cell of Minimum Value
Just like you can find minimum value, you can also extract the adjacent cell of the minimum value.
Steps:
➤ Select the output cell C12.
➤ In this case, the formula will be.
=VLOOKUP(MIN($C$5:$C$10),$C$5:$D$10,2,FALSE)
➤ Press ENTER.
As we have used col_index_num => 2, so you will find the Minimum Grade which is Grade D.
1.3. Return Multiple Values with VLOOKUP
You can also get multiple values with this functions. For getting multiple values like the grade and subject for a minimum mark in the following table, follow the steps below.
Steps:
➤ Select the output cell B13.
➤ Type the following formula-
=VLOOKUP(MIN(C5:C10),$C$5:$E$10,{2,3},FALSE)
 💡 Formula Breakdown
MIN(C5:C10) will return the minimum value in the range of C5:C10.
Then it will be the lookup value for the VLOOKUP function, $C$5:$E$10 is the table array, {2,3} is the array of the column index number and FALSE is for Exact Match
➤ Press ENTER.
After that, you will find the Minimum Grade which is D, and the corresponding subject Physics.
Method 2: Using INDEX-MATCH Function
For knowing the student’s name who has got the minimum number you can use the INDEX function and the MATCH function in this example.
Steps:
➤ Select the Cell C12
➤ Type the following formula
=INDEX(B5:B10,MATCH(MIN(C5:C10),C5:C10,0))
 💡  Formula Breakdown
MIN(C5:C10) will return the minimum value in the range of C5:C10
Then it will be the lookup value for the MATCH function, C5:D10 is the lookup array, and 0 is for Exact Match.
Then it will be used by the INDEX function and here B5:B10 is the range of student’s name that you want as a result.
➤Press ENTER
Then you will find the name of the student who has got the minimum mark in this case.
Read More: How to Find Lowest 3 Values in Excel
Method 3: Find nth Minimum Value with VLOOKUP
Let’s assume, you want to know the 2nd or 4th lowest grade of the students. To know the nth minimum value you can use the VLOOKUP function and the SMALL function.
Steps:
➤ Select the output cell C12.
➤ Apply the following formula to find the 2nd minimum value.
=VLOOKUP(SMALL(C5:C10,2),C5:D10,2,FALSE)
💡 Formula Breakedown
SMALL(C5:C10,2) will return the 2nd minimum value in the range C5:C10, where 2 is the kth value.
Then it will be the lookup value for the VLOOKUP function, C5:D10 is the table array, 2 is the column index number which is the Grade column here and FALSE is for Exact Match.
In this way, you will find the 2nd Minimum Grade which is Grade C.
➤ For getting the 4th minimum value, just change the second argument of the SMALL function.
=VLOOKUP(SMALL(C5:C10,2),C5:D10,4,FALSE)
Read More: How to Find Lowest Value with Criteria in Excel
Method 4: Using MINA with VLOOKUP Function
You can use the MINA function and the VLOOKUP function to get the Minimum Grade.
➤ Select the output cell C12.
➤ Type the following formula-
=VLOOKUP(MINA(C5:C10),$C$5:$D$10,2,0)
 💡  Formula Breakdown
MINA(C5:C10) will return the minimum value in the range of C5:C10.
Then it will be the lookup value for the VLOOKUP function, $C$5:$D$10 is the table array, 2 is the column index number which is the Grade column here and FALSE is for Exact Match.
Afterward, you will find the Minimum Grade which is Grade D.
Method 5: Using MIN and IF function
To know the minimum Marks in the following table you can use the MIN function and the IF function here.
➤ Select the output cell C12.
➤ Apply the following formula-
=MIN(IF($B$5:$B$10=B5:B10,$C$5:$C$10))
 💡  Formula Breakdown
B5:B10 is the range of Students and $C$5:$C$10 is the range of Marks.
$B$5:$B$10=B5:B10 is the logical test in the IF function and $C$5:$C$10 is the value when the logical test is TRUE.
Then, you will find the Minimum Marks which is 65.
Read More: How to Use Combined MIN and IF Function in Excel
Method 6: Use VLOOKUP to Find Minimum Value in a Row
In the following table, there are 3 rows for marks of 3 subjects. To know the minimum mark for any of the subjects you have to find the minimum value row-wise. You can use the MIN function and the VLOOKUP function for this purpose.
➤ Select the output cell C11.
➤ Apply the following formula-
=MIN(VLOOKUP(B11,$B$6:$F$8,{2,3,4,5},FALSE))
B11 is the lookup value, $B$6:$F$8 is the table array, {2,3,4,5} is the array of the column index number.
Afterward, you will find the minimum mark which is 65 for Chemistry.
Read More: How to Find Minimum Value That Is Greater Than 0 in Excel
How to Find Minimum Value with Multiple Criteria in Excel
Let’s say, you want to find the minimum Sales for the North Region and for Quantity>60. To do this you have to use the MIN function and the IF function here.
Steps:
➤Select the output Cell G6
➤Type the following formula
=MIN(IF(C5:C12=F6,IF(D5:D12>60,E5:E12)))
 💡 Formula Breakdown
C5:C12 is the range of Region, D5:D12 is the range of Quantity and E5:E12 is the range of Sales.
C5:C12=F6 is the first logical test in the IF function and IF(D5:D12>60, E5:E12) is the value when the logical test is TRUE.
➤Press ENTER and you will find the Minimum Sales which is $2,785.00.
Read More: How to Find Minimum Value Based on Multiple Criteria in Excel
Download Practice Workbook
Conclusion
In this article, I tried to cover the easiest ways to VLOOKUP minimum value in Excel effectively. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.
Related Articles
- How to Use MIN Function to Exclude Zero in Excel
- Excel MIN Function Returns 0
- Difference Between MAX and MIN Function in Excel
<< Go Back to Excel MIN Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
