How to VLOOKUP Minimum Value in Excel (Easiest 8 Ways)

If you are looking for some of the easiest ways to VLOOKUP minimum value in Excel, then you will find this article worth it.

While working with Excel it becomes necessary sometimes to find the lowest value in a dataset easily and quickly for calculations. By using the following methods you will be able to do this job effectively.

Download Workbook

8 Ways to VLOOKUP Minimum Value in Excel

I will explain the methods to VLOOKUP minimum value in Excel by using the following two data tables. In the first table, there are marks distribution of some students in a college and in the second data table, there are some records of sales of a company.

Using the following methods we will be able to 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.

Datatable

Method-1: VLOOKUP the Minimum Value and Return Adjacent Cell

If you want to know the grade achieved for a minimum mark then you can use the VLOOKUP function and the MIN function.

VLOOKUP minimum value and return adjacent cell

Step-01:
➤Select the output Cell C12
➤Type the following formula

=VLOOKUP(MIN($C$5:$C$10),$C$5:$D$10,2,FALSE)

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, 2 is the column index number which is the Grade column here and FALSE is for Exact Match

VLOOKUP minimum value and return adjacent cell

➤Press ENTER

Result:
Then you will find the Minimum Grade which is Grade D.

VLOOKUP minimum value and return adjacent cell

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.

INDEX-MATCH function

Step-01:
➤Select the output Cell C12
➤Type the following formula

=INDEX(B5:B10,MATCH(MIN(C5:C10),C5:C10,0))

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 which you want as a result.

INDEX-MATCH function

➤Press ENTER

📓 Note
For other versions except Microsoft Excel 365, you have to press CTRL+SHIFT+ENTER

Result:
Then you will find the name of the student who has got the minimum mark in this case.

INDEX-MATCH function

Method-3: VLOOKUP nth Minimum Value

Let’s assume, you want to know the 2nd or 3rd lowest grade of the students. To know the nth minimum value you can use the VLOOKUP function and the SMALL function.

VLOOKUP nth minimum value

Step-01:
➤Select the output Cell C12
➤Type the following formula

=VLOOKUP(SMALL(C5:C10,2),C5:D10,2,FALSE)

SMALL(C5:C10,2) will return the 2nd minimum value in the range C5:C10, here 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

VLOOKUP nth minimum value

➤Press ENTER

Result:
In this way, you will find the 2nd Minimum Grade which is Grade C.

VLOOKUP nth minimum value

Method-4: Using 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.

minimum value in a row

Step-01:
➤Select the output Cell C11
➤Type 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.

minimum value in a row

➤Press ENTER

📓 Note
For other versions except Microsoft Excel 365, you have to press CTRL+SHIFT+ENTER

Result:
Afterward, you will find the minimum mark which is 65 for Chemistry.

minimum value in a row

Method-5: VLOOKUP and Return Multiple Value

For getting multiple values like the grade and subject for a minimum mark in the following table you can use the VLOOKUP function and the MIN function.

return multiple values

Step-01:
➤Select the output Cell B13
➤Type the following formula

=VLOOKUP(MIN(C5:C10),$C$5:$E$10,{2,3},FALSE)

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

return multiple values

➤Press ENTER

📓 Note
For other versions except Microsoft Excel 365, you have to press CTRL+SHIFT+ENTER

Result:
After that, you will find the Minimum Grade which is D and the corresponding subject Physics.

VLOOKUP minimum value

Method-6: Using MINA to Get the Minimum Value

You can use the MINA function and the VLOOKUP function to get the Minimum Grade

using MINA function

Step-01:
➤Select the output Cell C12
➤Type the following formula

=VLOOKUP(MINA(C5:C10),$C$5:$D$10,2,0)

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

using MINA function

➤Press ENTER

Result:
Afterward, you will find the Minimum Grade which is Grade D.

using MINA function

Method-7: 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.

using MIN and IF function

Step-01:
➤Select the output Cell C12
➤Type the following formula

=MIN(IF($B$5:$B$10=B5:B10,$C$5:$C$10))

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.

VLOOKUP minimum value

➤Press ENTER

Result:
Then, you will find the Minimum Marks which is 65.

VLOOKUP minimum value

Method-8: VLOOKUP the Minimum Value with Multiple Criteria

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.

Multiple criteria

Step-01:
➤Select the output Cell G6
➤Type the following formula

=MIN(IF(C5:C12=F6,IF(D5:D12>60,E5:E12)))

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.

Multiple criteria

➤Press ENTER

Result:
Then, you will find the Minimum Sales which is $2,785.00.

Multiple criteria

Practice Section

For doing practice by yourself we have provided a Practice section like below for each method in each sheet on the right side. Please do it by yourself.

practice

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.

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo