Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Find Minimum Value with VLOOKUP in Excel (6 Ways)

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.

VLOOKUP Minimum Value


Download Practice Workbook


6 Effective Ways to Find Minimum Value with VLOOKUP in Excel

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.

Dataset

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

Minimum Value with VLOOKUP

➤ Press ENTER

Then you will find the Minimum Mark which is 65.

Finding Minimum Value


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)

Formula

➤ Press ENTER

Adjacent Cell of Minimum Value

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 to Find Multiple Values

 💡  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

Find Multiple Values with VLOOKUP

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

Read More: How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)


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))

INDEX MATCH Function

 💡  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

VLOOKUP Minimum Value with INDEX MATCH Function

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 (5 Easy Methods)


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)

Finding 2nd Minimum

💡 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)

Finding 4th Lowest Value


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)

Using MINA with VLOOKUP

 💡  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))

MIN and IF Function to Find Minimum Value

 💡  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.


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.

Dataset

➤ Select the output Cell C11

➤ Apply the following formula

=MIN(VLOOKUP(B11,$B$6:$F$8,{2,3,4,5},FALSE))

Minimum Value in a Row

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.


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.

Sample Data

Steps:

➤Select the output Cell G6

➤Type the following formula

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

Formula

 💡  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.

Minimum Value with Multiple Criteria

Read More: How to Find Minimum Value Based on Multiple Criteria in Excel


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

Tanjima Hossain

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