+ Reply to Thread
Results 1 to 7 of 7

Two ranges, and match, and a corresponding date

  1. #1
    Registered User
    Join Date
    09-23-2012
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    63

    Two ranges, and match, and a corresponding date

    Hi. Any help would be appreciated. See spreadsheet example.

    Objective, I am looking for a date value in cell B4. The date in the example I am looking for is 02/28/2016. How I get that is the help I need.
    In Row-2 (Dataset-A) the numbers from month to month stay the same. Maybe every 6-12 months it will have an increment.
    In Row-3 (Dataset-B) the numbers will grow (99% of the time) in a very inconsistent manor.
    How do I find the number in Row-3 (Dataset-B) that closest matches Row-2 (Dataset-A) without going over? Once that is determined, how do I find the corresponding date from that column Row-1?

    See example excel sheet I am referencing.
    https://drive.google.com/file/d/0B6_...ew?usp=sharing

    Thank you,
    Dan
    Attached Files Attached Files
    Last edited by dbrownla; 03-12-2016 at 09:24 AM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Two ranges, and match, and a corresponding date

    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced (next to quick post),
    • Scroll down until you see "Manage Attachments",
    • Click that then select "add files" (top right corner).
    • Click "Select Files" find your file, click "open" click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!
    Ben Van Johnson

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Two ranges, and match, and a corresponding date

    Hey Dan,

    In B4 put this formula:

    =INDEX($B$1:$Z$1,MIN(IF($B$2:$M$2<$B$3:$M$3,COLUMN($B$2:$M$2),""))-2)

    BUT you need to confirm the formula with a Control+Shift+Enter (CSE) keystroke(s). See the attached and lookup Array Formulas.
    CSE Horizontal Index (Condition Match).xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    09-23-2012
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    63

    Re: Two ranges, and match, and a corresponding date

    protonLeah, thank you. I added the attachment.

    MarvinP, thank you. That did produce the correct date but I have another question.

    If I change Cell M2 to 160,000. I was hoping the B4 date cell would now reference date 7/31/2016 since M3 would be the next closest number. Basically to find the largest number in Row2 and then find the closest number to that (without going over) in Row3. Is that possible?

    Thank you,
    dan
    Last edited by dbrownla; 03-12-2016 at 12:04 PM.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Two ranges, and match, and a corresponding date

    Hi,

    The biggest "without going over" means you need to find the first one that goes over. If the last one in a row is that answer, you can't back up a column as there is nothing in the column to the right.

  6. #6
    Registered User
    Join Date
    09-23-2012
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    63

    Re: Two ranges, and match, and a corresponding date

    Ok, if I am understanding correctly, I'll need at least two months worth of the same increased amount of data in row-2. Not a problem at all in this situation. I changed the following cells to 140,000. K2, L2, and M2.
    I then tried that formula again hoping date 04/30/2016 would pop up. Is there a way to make this work? Again, I appreciate all the help.

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Two ranges, and match, and a corresponding date

    OK Dan,

    Let me try this one more time. Without needing to back up a column. See the attached where I've transposed your data, put in a Helper Column and a formula. Then this formula gives you what I think you want.

    =INDIRECT(ADDRESS(MAX(IF(D10:D34=FALSE,ROW(D10:D34),0)),1)) (entered as a CSE formula)

    CSE Horizontal Index (Condition Match) with Helper for Dan.xlsx

    I hope this gives you enough help to do your problem like you want it done.

+ 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. Index Match or Vlookup on Date Ranges
    By nmoline in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-17-2015, 06:00 PM
  2. INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges
    By Andrew-Mark in forum Excel General
    Replies: 3
    Last Post: 02-27-2015, 10:56 PM
  3. INDEX / MATCH FUNCTIONS - Multiple Criteria Between Date Ranges
    By JMData Consultant in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-22-2014, 02:25 PM
  4. Using Index & Match with Date Ranges
    By Maristar in forum Excel General
    Replies: 8
    Last Post: 07-09-2014, 09:33 PM
  5. [SOLVED] INDEX date that MATCH on date range, MATCH code(string)
    By Dahlia in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2014, 03:33 AM
  6. Replies: 2
    Last Post: 08-16-2013, 10:18 AM
  7. index match 1 row 2 columns, date ranges included
    By distribution master in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-26-2012, 01:47 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