Results 1 to 7 of 7
Like Tree1Likes
  • 1 Post By Jonmo1

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. #1
    Board Regular
    Join Date
    Oct 2009
    Posts
    432

    Post 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. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,542

    Default 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..
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    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

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Posts
    432

    Post 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. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,542

    Default Re: Vlookup Return Minimum Value

    Quote Originally Posted by Beachson View Post
    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}
    Superstar31 likes this.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    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

  5. #5
    Board Regular
    Join Date
    Oct 2009
    Posts
    432

    Post 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. #6
    Board Regular
    Join Date
    Oct 2009
    Posts
    432

    Post Re: Vlookup Return Minimum Value

    Ok got it thanks very much!

  7. #7
    Board Regular
    Join Date
    Nov 2005
    Location
    Somewhere over the Rainbow
    Posts
    440

    Default Re: Vlookup Return Minimum Value

    Quote Originally Posted by Jonmo1 View Post
    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?
    Teach Me, Show me, Please Just Help Me

    P.S. Thank You & You're Welcome In Advance

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com