+ Reply to Thread
Results 1 to 11 of 11

MAX with VLOOKUP

  1. #1
    Registered User
    Join Date
    04-09-2017
    Location
    UAE
    MS-Off Ver
    MS365 Version 2301
    Posts
    43

    MAX with VLOOKUP

    To anyone reading this, i need to find the MAX value of Column B and display it in C1 including the corresponding date from Column A. In the attached sample sheet, C1 should display "$8,442 on 03-Jul-18". I tried to use INDEX and MAX, but I am only able to display the Max amount and I don't know how to include the date or if this is even possible. thanks!
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: MAX with VLOOKUP

    Hi,

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: MAX with VLOOKUP

    Maybe this:

    ="$"&MAX(B2:B11)&" on "&TEXT(INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0)),"dd-mm-yyyy")

    Or this:

    =TEXT(MAX(B2:B11),"$0,00")&" on "&TEXT(INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0)),"dd-mm-yyyy")

    BSB
    Last edited by BadlySpelledBuoy; 01-26-2020 at 06:37 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: MAX with VLOOKUP

    Try this:

    ="$"&MAX($B$2:$B$11)&" on "&TEXT(INDEX($A$2:$A$11,MATCH(MAX($B$2:$B$11),$B$2:$B$11,0)),"dd-mmm-yy")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: MAX with VLOOKUP

    non traditional way
    =MOD(MAX(INDEX(B2:B11+A2:A11%%%,)),1)/1%%%
    But there is restriction B column is only integer and les then 999 999 999

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: MAX with VLOOKUP

    C1 should display "$8,442 on 03-Jul-18".
    The solutions in posts #2 and #5 only return the date.

  7. #7
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: MAX with VLOOKUP

    The solutions in posts #2 and #5 only return the date.
    Yes but #4 return "$8442 on 03-Jul-18"

  8. #8
    Registered User
    Join Date
    04-09-2017
    Location
    UAE
    MS-Off Ver
    MS365 Version 2301
    Posts
    43

    Re: MAX with VLOOKUP

    Quote Originally Posted by AliGW View Post
    The solutions in posts #2 and #5 only return the date.
    thank you all so much for your help! you just saved my day. AliGW thank you! this is what i'm looking for.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: MAX with VLOOKUP

    Quote Originally Posted by BMV View Post
    Yes but #4 return "$8442 on 03-Jul-18"
    What's your point?

    Your suggestion doesn't meet the requirements, but mine does. If the OP wants the comma (is that what you are implying?), then it can easily be added using formatting in the formula.
    Last edited by AliGW; 01-26-2020 at 07:16 AM.

  10. #10
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: MAX with VLOOKUP

    The second formula in post #3 meets the requirements AND has the comma... Just saying

    BSB

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: MAX with VLOOKUP

    Yes, it does indeed!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Vlookup - keeping the leading zeros in a cell to allow the vlookup to work
    By UFBEE1970 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-02-2019, 04:41 PM
  2. Replies: 5
    Last Post: 12-07-2016, 09:18 AM
  3. Replies: 0
    Last Post: 12-26-2013, 01:12 PM
  4. Display Cell within VLookup range that excel thinks matches the VLookup Value
    By headachexcelperson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2013, 04:56 PM
  5. how to bring all vlookup returns even with duplicate vlookup search keys
    By NYC4LIFE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-06-2013, 04:53 PM
  6. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  7. Replies: 5
    Last Post: 07-29-2009, 07:53 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1