Vlookup Return Minimum Value

[Beachson]

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

=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!

 

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

 

[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... ?

 

[Jonmo1]

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}

 

[Beachson]

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)

 

[Beachson]

Ok got it thanks very much!

 

[Superstar31]

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?

{=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?

 

[Sumanmathew]

Ok got it thanks very much!

=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!

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)

 

[Fluff]

That's exactly what the formula in post#2 does.