# Vlookup Return Minimum Value

This is a discussion on Vlookup Return Minimum Value within the Excel Questions forums, part of the Question Forums category; Here is my starting Vlookup Formula I. I need to alter it as I will state after the formula... Code: ...

1. ## Vlookup Return Minimum Value

Here is my starting Vlookup Formula I. I need to alter it as I will state after the formula...

Code:
```
=Vlookup(A2,REPORT!\$A\$2:\$D\$10000,4,0)```
Instead of returning the data from column D upon the first match I need the smallest available number to be returned. There will be more than one match and they will all have different numbers to return so I need the smallest return from D.

So in "REPORT" there is many duplicates in Columns A but they all have different values in Column D and I need to return the smallest number from Column of all of those duplicates in Column A. Thanks!

2. ## Re: Vlookup Return Minimum Value

Try this array formula enterd with CTRL + SHIFT + ENTER

=MIN(IF(REPORT!\$A\$2:\$A\$10000=A2,REPORT!\$D\$2:\$D\$10000))

Hope that helps.

Note, if there are blank results of the vlookup, they will be counted as 0.
It can be adjusted if needed..

3. ## Re: Vlookup Return Minimum Value

It returned one number for everything. It seems that it returned the smallest number from Column D in general but I need the smallest return for each specific match... ?

4. ## Re: Vlookup Return Minimum Value

Originally Posted by Beachson
It returned one number for everything. It seems that it returned the smallest number from Column D in general but I need the smallest return for each specific match... ?
Make sure you enter the formula with CTRL + SHIFT + ENTER

After entering the formula, highlight the cell with the formula and press F2
Then Press CTRL + SHIFT + ENTER

When entered correctly, the formula will be enclosed in {brackets}

5. ## Re: Vlookup Return Minimum Value

I need the smallest return for A2 but I only want to consider Values in Column D when A2 matches say maybe 10 Cells in column A of the other sheet. Not the smallest number in general from Column D

So if the lookup in A2 of sheet 1 is Dog and in sheet 2 there is 5 Cats and 5 Dogs and the values for the 5 dogs is (1,2,3,4,5) I want it to match dog and pick the smallest available number (1)

6. ## Re: Vlookup Return Minimum Value

Ok got it thanks very much!

7. ## Re: Vlookup Return Minimum Value

Originally Posted by Jonmo1
Try this array formula enterd with CTRL + SHIFT + ENTER

=MIN(IF(REPORT!\$A\$2:\$A\$10000=A2,REPORT!\$D\$2:\$D\$10000))

Hope that helps.

Note, if there are blank results of the vlookup, they will be counted as 0.
It can be adjusted if needed..
How would you adjust for zero?

Also is there any reason this wouldn't work?

Code:
`{=MIN(IF(NewData!\$E\$2:\$E\$17076=A2,NewData!\$J\$2:\$J\$17076),IF(OldData!\$B\$2:\$B\$816=A2,OldData!\$D\$2:\$D\$816))}`
I'm having it search both the old and new data and give me the lowest data between two different sheets?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•