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..
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: ...
Here is my starting Vlookup Formula I. I need to alter it as I will state after the formula...
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.Code:=Vlookup(A2,REPORT!$A$2:$D$10000,4,0)
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!
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..
Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac
The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Ferris Bueller A.K.A. John Hughes, 1986
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... ?
Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac
The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Ferris Bueller A.K.A. John Hughes, 1986
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)
Ok got it thanks very much!
How would you adjust for zero?
Also is there any reason this wouldn't work?
I'm having it search both the old and new data and give me the lowest data between two different sheets?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))}
Teach Me, Show me, Please Just Help Me
P.S. Thank You & You're Welcome In Advance
Like this thread? Share it with others