+ Reply to Thread
Results 1 to 5 of 5

Index / Match across multiple columns

Hybrid View

  1. #1
    Registered User
    Join Date
    06-07-2013
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Index / Match across multiple columns

    Not sure if I have the title right. Here's what I'm looking for.

    Columns A to G contains days of the week formatted to show only the DD. Column H is the week number and may not follow standard convention. I need to input a date in Column I and have it look up the date in columns A to G and then return the week # from Column H into Column J.

    Index/Match formula as I know it is limited to indexing a range, then matching to a column in that range. Need a formula to search the entire range - A2 to G54 (or so) and return the week # from Column H.

    M T W T F S S Wk# Input Date Wk# (result)
    30 31 01 02 03 04 05 1 01/08/14 2
    06 07 08 09 10 11 12 2

    Thoughts?

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Index / Match across multiple columns

    If the cells really do contain the full date (eg, year+month+day) formatted to only display the days,
    AND
    the cells increment calander-like so that it increments across and then the first column in the n+1 row is the day after the last column in the nth row;
    AND
    the dates are in ascending order, eg they increase as you go down row-to-row;

    Then you can actually solve this with a regular index(match()); just use a MATCH(,,1) instead of a MATCH(,,0); guarantee it will find the row below it by knocking one day off the lookup date, and then add that row back in the index. That should just about do it, I think.

    J1 = INDEX(H1:H100, MATCH(I1 -1, G1:100, 1) +1 )

  3. #3
    Registered User
    Join Date
    06-07-2013
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Re: Index / Match across multiple columns

    Mmmm....maybe I'm not quite getting it. And I should have attached the file to start with. Here's the file I'm working with.
    Attached Files Attached Files

  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Index / Match across multiple columns

    So looking at your data, everything is a function of the start date.

    So there are three logically equivalent things you could do, each of which would deliver the number you want:

    K4 = INDEX(H2:H54, MATCH(K3-1, G2:G54, 1) +1)
    //As above: compare date-1 to find the week before the week, then deliever the week after that one. Now that I test it with your data, I realize this will deliver #N/A for dates in Week 1, so I won't recommend this one.

    K4 = INDEX(H2:H54, MATCH(K3, A2:A54, 1) )
    //Finds the largest date of start-of-week that is equal to or less than the input date (eg, finds that week's Monday); then returns the matching week number.

    K4 = TRUNC((K3 - A2) / 7, 0) +1
    //No lookup!: subtracts to get the days between input and start date, then turns that into the week number since start.
    Last edited by ben_hensel; 11-05-2013 at 11:29 AM. Reason: added comments to pseudocode

  5. #5
    Registered User
    Join Date
    06-07-2013
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Re: Index / Match across multiple columns

    EXCELLENT! Makes it easy! Either of the last 2 works just fine. Thanks Ben for your 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. INDEX MATCH with multiple columns
    By there83 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-08-2013, 11:33 PM
  2. [SOLVED] Index Match multiple columns
    By Bravo33 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-07-2013, 10:42 AM
  3. [SOLVED] Index/Match across multiple columns
    By BB1972 in forum Excel General
    Replies: 2
    Last Post: 07-27-2012, 12:05 PM
  4. Index/Match but with multiple columns?
    By notleonardo in forum Excel General
    Replies: 4
    Last Post: 11-17-2011, 07:16 PM
  5. [SOLVED] Index/Match from multiple columns
    By hgopp99 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-21-2006, 02:15 PM

Tags for this Thread

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