+ Reply to Thread
Results 1 to 7 of 7

VLOOKUP type formula to return result that is less than adjacent cell

  1. #1
    Registered User
    Join Date
    02-07-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    4

    VLOOKUP type formula to return result that is less than adjacent cell

    I have a list of Account Numbers and Order Dates, and another list of Account Numbers and Activity Dates. I need to find the Activity Date that is less than but as close as possible to the Order Date for each Account Number. Each account will not necessarily have an Activity Date, and should not return an approximate match if no exact match is present. In the attached screenshot, the green and red boxes in the attached screenshots are the values I am trying to isolate. Thank you for any assistance you can offer.


    Capture.JPG

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: VLOOKUP type formula to return result that is less than adjacent cell

    Enter formula in C2 and copy down

    Format as Date

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

    v A B C D E F
    1
    2 XYZ 12/18/2017 12/13/2017 XYZ 12/12/2017
    3 ABC 1/21/2018 1/19/2018 XYZ 12/13/2017
    4 XYZ 12/21/2017
    5 ABC 1/10/2018
    6 ABC 1/17/2018
    7 ABC 1/18/2018
    8 ABC 1/19/2018
    9 ABC 1/24/2018
    10 ABC 1/26/2018

    you can also use VLOOKUP
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by AlKey; 02-07-2018 at 04:31 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    02-07-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    4

    Re: VLOOKUP type formula to return result that is less than adjacent cell

    Thank you! Exactly what I was looking for.

  4. #4
    Registered User
    Join Date
    02-07-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    4

    Re: VLOOKUP type formula to return result that is less than adjacent cell

    Actually, I spoke too soon. This returns the date closest to the referenced date. I need to find the date closest to the referenced date for the specific account. I have attached an another screenshot for more context.

    Capture3.JPG
    Last edited by FFFAdam; 02-07-2018 at 04:59 PM.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: VLOOKUP type formula to return result that is less than adjacent cell

    Use this array formula
    Enter in C2
    ***Array formula
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
    You will know the array is active when you see curly braces { } appear around your formula.
    If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: VLOOKUP type formula to return result that is less than adjacent cell

    Also

    =LOOKUP(B2,F$2:F$10)

  7. #7
    Registered User
    Join Date
    02-07-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    4

    Re: VLOOKUP type formula to return result that is less than adjacent cell

    That worked as I needed. Thanks again, AlKey!

+ 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. [SOLVED] how to check the adjacent cell of the COUNTIF result in a formula
    By ulothar in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-30-2017, 04:29 AM
  2. [SOLVED] Basic formula help. How to type in a cell and then display adjacent cell.
    By cjt82 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-22-2017, 06:28 AM
  3. [SOLVED] Formula that will result the cell blank based on adjacent cell
    By LShepherd in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-20-2016, 01:04 PM
  4. Replies: 3
    Last Post: 07-21-2015, 05:10 PM
  5. How to return a blank result when using SUM(VLOOKUP formula??
    By Shannon561 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-13-2014, 03:49 AM
  6. [SOLVED] Return a text string when the result of VLOOKUP formula is #N/A
    By jeremy nickels in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-04-2006, 12:30 PM
  7. [SOLVED] Advanced formula - Return result & Show Cell Reference of result
    By Irv in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-05-2006, 10:40 PM

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