+ Reply to Thread
Results 1 to 3 of 3

Excel formula based on index match between dates

  1. 12-27-2018, 04:26 AM #1
    Sondai
    Sondai is offline
    Registered User
    Join Date
    10-30-2018
    Location
    INDIA
    MS-Off Ver
    MS OFFICE 2013
    Posts
    12

    Post Excel formula based on index match between dates

    Problem Description:
    I want to get the Product code in then REPORT SHEET from the data sheets based on the inventory location (selected through drop down list) and also by the Start & End Date.

    I Tried this formula : =IFERROR(IF($B$7="OFF","",INDEX(Table1[PRODUCT CODE],MATCH(1,((Table1[LOCATION]=REPORT!$G$2)*(Table1[DATE]>=REPORT!$B$2)*(Table1[DATE]>=REPORT!B4)),0))),"")

    but it is not working and giving repeated values. please help me out.

    I have attached the sample file please go through that in details.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-27-2018, 04:35 AM #2
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Excel formula based on index match between dates

    May be try

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


    ...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.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  3. 12-27-2018, 04:47 AM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Excel formula based on index match between dates

    An ordinary formula. Use variants of this in E5, copied down:

    =IFERROR(IF($B$7="OFF","",INDEX('PRODUCTION ENTRY SHEET'!B:B,AGGREGATE(15,6,ROW(Table1[[PRODUCT CODE]:[PRODUCT CODE]])/((Table1[[DATE]:[DATE]]>=REPORT!$B$2)*(Table1[[DATE]:[DATE]]<=REPORT!$B$4)*(Table1[[LOCATION]:[LOCATION]]=REPORT!$G$2)),ROWS(E$5:E5)))),"")

    I do not know where you want to import the answers for columns K & L from. You did not say and did not supply a manually calculated answer.
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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 - Trying to pull information from two worksheets based on matching dates
    By Wrayden in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 01-02-2018, 10:25 PM
  2. [SOLVED] Excel - INDEX MATCH BETWEEN DATES
    By ironfelix717 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-18-2017, 03:24 PM
  3. [SOLVED] INDEX/ MATCH formula to return multiple dates
    By lukela85 in forum Excel General
    Replies: 5
    Last Post: 10-25-2017, 07:07 AM
  4. Creating INDEX MATCH MATCH formula based off text in cells
    By bbkdude in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-26-2017, 10:37 AM
  5. Match/Index maybe? Formula to pull a cell in column A based on a match for two columns
    By Lsxtrkiller in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-14-2017, 07:47 PM
  6. [SOLVED] IFERROR, INDEX, MATCH... formula... that also reads dates.
    By JasonNeedsHelp in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 07-22-2014, 03:52 PM
  7. [SOLVED] Index and Match based on expiry dates in multiple sheets
    By Mr.Fatcat in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-10-2014, 04:58 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1