+ Reply to Thread
Results 1 to 3 of 3

Index Match with dates and multiple criteria

  1. #1
    Forum Contributor
    Join Date
    05-05-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2013
    Posts
    103

    Index Match with dates and multiple criteria

    Hello,
    I am trying to use an index match with dates for column BB on the Direct Sales Companies tab. I am trying to pull the value on US LEI tab Column C, that matches the year and month from Column B on the Direct Sales Companies tab. For example, cell BB3 on the Direct Sales Companies tab should return the value 106.1 from the US LEI tab because the month and year in column B of the direct sales companies tab matches the month and year on the US LEI tab. I am using the following formula, which is not returning the correct values:

    ={INDEX('US LEI'!$C$1:$C$179,MATCH(1,(MONTH('US LEI'!$B$1:$B$179)=MONTH('Direct Sales Companies'!$B3))*(YEAR('US LEI'!$B$1:$B$179)=YEAR('US LEI'!$B$1:$B$179)),1))}

    Any suggestions?

    Thanks!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-09-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: Index Match with dates and multiple criteria

    Hi,

    Try below array formula:

    =INDEX('US LEI'!$C$2:$C$179,MATCH(1,(MONTH('US LEI'!$B$2:$B$179)=MONTH('Direct Sales Companies'!$B3))*(YEAR('US LEI'!$B$2:$B$179)=YEAR($B3)),0))

    Confirm with Ctrl+Shift+Enter.

    Regards,

  3. #3
    Forum Contributor
    Join Date
    05-05-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2013
    Posts
    103

    Re: Index Match with dates and multiple criteria

    Thank you!

+ 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. Lookup with criteria / Index match (?) / Using dates
    By RenanCip in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-28-2014, 11:23 AM
  2. [SOLVED] Sum Index Match Dates Between Multiple Date Criteria
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-30-2014, 08:54 PM
  3. Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  4. Multiple Criteria Index/Match Function in VBA Looking Up Dates/Strings/Numbers
    By nadstradamus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-21-2014, 12:35 PM
  5. INDEX MATCH with multiple same dates
    By mcayea in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 07-08-2013, 10:58 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