+ Reply to Thread
Results 1 to 12 of 12

Lookup - Return first column based on second

  1. #1
    Registered User
    Join Date
    04-24-2004
    Location
    Wisconsin, USA
    MS-Off Ver
    2007
    Posts
    76

    Lookup - Return first column based on second

    I have two columns with DATE and VALUE. I want to return the DATE based the highest value in VALUE. I have tried vLookup, based on my array; however, I want to lookup the second column and return the first. Is this possible?

    I have attached a small sample.

    Thanks,
    Steve K.
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Lookup - Return first column based on second

    Try...

    =INDEX(A7:A12,MATCH(B2,B7:B12,0))
    HTH
    Regards, Jeff

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,192

    Re: Lookup - Return first column based on second

    try

    =INDEX($A$7:$A$12,MATCH($B$2,$B$7:$B$12,0))

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Lookup - Return first column based on second

    =INDEX($A$7:$A$12,MATCH(B2,$B$7:$B$12,0))
    formatted as date

    edit: must be an echo (and lag) in here lol
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lookup - Return first column based on second

    Quote Originally Posted by esskaykay View Post
    I have tried vLookup.
    I would use the INDEX/MATCH version as others have suggested but since I'm bored here's how you could do it with VLOOKUP...

    =VLOOKUP(B2,CHOOSE({1,2},B7:B12,A7:A12),2,0)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    04-24-2004
    Location
    Wisconsin, USA
    MS-Off Ver
    2007
    Posts
    76

    Re: Lookup - Return first column based on second

    Thank you very much Jeff and everyone else - all suggestions worked perfectly.

    Again, my thanks,
    Steve K.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Lookup - Return first column based on second

    happy to help

    If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

  8. #8
    Registered User
    Join Date
    04-24-2004
    Location
    Wisconsin, USA
    MS-Off Ver
    2007
    Posts
    76

    Re: Lookup - Return first column based on second

    Not to beat a dead horse here but I have one other question on this if you please.

    I'd like to have the date display between parenthesis - ( 01/01/2017 ). I used ="( "&code&" )" which returned what I wanted except it displays the value not the date even though I have the field formatted as date example ( 42742 ). I understand why this happens but would like format the internal part of the expression.

    Thanks,
    SKK

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Lookup - Return first column based on second

    That will return text, you need to convert the date to text as well...
    ="( "&text(INDEX($A$7:$A$12,MATCH(B2,$B$7:$B$12,0)),"mm/dd/yyyy")&" )"

  10. #10
    Registered User
    Join Date
    04-24-2004
    Location
    Wisconsin, USA
    MS-Off Ver
    2007
    Posts
    76

    Re: Lookup - Return first column based on second

    Perfect - that did it.

    Thank you again,
    SKK

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lookup - Return first column based on second

    You're welcome. We appreciate the feedback!

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Lookup - Return first column based on second

    Happy to help

+ 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. Two Lookup and Return (Lookup the column, then lookup the row)
    By Branbran10 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-05-2015, 04:55 PM
  2. Replies: 2
    Last Post: 05-07-2015, 04:38 PM
  3. Replies: 1
    Last Post: 11-13-2013, 04:03 PM
  4. Replies: 4
    Last Post: 03-13-2013, 12:38 PM
  5. Replies: 1
    Last Post: 01-05-2011, 05:18 PM
  6. Replies: 7
    Last Post: 04-16-2009, 01:03 PM
  7. VLOOKUP: Return value from column left of lookup column?
    By XiaoWei in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-27-2008, 06:17 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