Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Trying to use Index-Match with criteria and between dates

  1. #1
    Board Regular
    Join Date
    Aug 2016
    Location
    Toms River NJ
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Trying to use Index-Match with criteria and between dates

    Hello All!!
    I have been searching the forum, came across similar problems and various formulas and I for the life of me cannot get them to work.
    I have used Index and Match and IFError in formulas and they are great. I donot think that Vlookup will work unless I arrange the Dates ...
    I have 2 Tables.
    Table1 has the Data I am trying to retrieve.
    The Headers are: Store#, Invoice#,InvDate,PackSlip,Ven,Part#,Cost, CoreCost,TotalCost,Credit Type,Notes

    table2 has the Reference Material and Fields for Retrieved Data
    The Headers are:Close Date,Search Date, Store#,Vendor,Line,Part#, PInv#,P InvDate,P PckSlp,CreditAmount$

    The first part of data that i am trying to retrieve is PInv#. But I need to Match the Part# during a Date Range. The "Start Date" would be the Close Date and then "End Date" would be Search Date. the field that would be checked would be InvDate on Table1

    I have tried using array and standard and I am lost...

    Any and all kicks in the right direction is greatly appreciated!!

  2. #2
    Board Regular
    Join Date
    Aug 2016
    Location
    Toms River NJ
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to use Index-Match with criteria and between dates

    Example of Formula that I am trying to use(Please note Headers are different but essential the same) [Code]=INDEX(PrimeInv[Invoice '#],MATCH(1,(PrimeInv[Inv Date]>=[Close Date])*(PrimeInv[Inv Date]<=[Search Date])*(PrimeInv[Part '#]=[Part'#]),0))[CODE/]

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,954
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Trying to use Index-Match with criteria and between dates

    Hard to help without a data sample for testing purposes.
    Anyway see if this works
    =INDEX(PrimeInv[Invoice'#],MATCH(1,(PrimeInv[InvDate]>=[@[Close Date]])*(PrimeInv[InvDate]<=[@[Search Date]])*(PrimeInv[Part'#]=[@[Part'#]]),0))
    Ctrl+Shift+Enter

    M.

  4. #4
    Board Regular
    Join Date
    Aug 2016
    Location
    Toms River NJ
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to use Index-Match with criteria and between dates

    Also to note is that on Table1 the InvDate column has the Date Repeated for each row...

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,954
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Trying to use Index-Match with criteria and between dates

    Have you tried the formula in post 3?

    M.

  6. #6
    Board Regular
    Join Date
    Aug 2016
    Location
    Toms River NJ
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to use Index-Match with criteria and between dates

    Quote Originally Posted by Marcelo Branco View Post
    Have you tried the formula in post 3?

    M.
    Yes, we must have been cross posting... I used the formula that you gave me and entered it as an array. I have a number of #NA , but that is the least of my worries... Thank you Marcelo Branco!!!
    I can send a scrubbed version and more detail if that would be helpful...

    The Search Date may cause me a little headache in matching data... I only have Table for the one table for June... the other table with the Close Date and Search Date is from Jan1 2018 to current...
    If i wanted to add another Criteria I assume that i can add it at the end? or before the Part# part of the match? I want to match Line on Table 2 with Vendor On Table 1.. just to make sure that if a part # is in 2 lines that it grabs the correct data...

  7. #7
    Board Regular
    Join Date
    Aug 2016
    Location
    Toms River NJ
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to use Index-Match with criteria and between dates

    Marcelo Branco, Quick Question... Formula is working but its not catching everything... I have a data entry that is on Prime Table but since it has a date of 6/18/2018 the formula does not pick it up... How can i correct that?

  8. #8
    Board Regular
    Join Date
    Aug 2016
    Location
    Toms River NJ
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to use Index-Match with criteria and between dates

    Quote Originally Posted by arossijr View Post
    Marcelo Branco, Quick Question... Formula is working but its not catching everything... I have a data entry that is on Prime Table but since it has a date of 6/18/2018 the formula does not pick it up... How can i correct that?
    Marcelo, Disregard!! I found the issue for this part... some of the Part Numbers where not set as a Number... oops...

  9. #9
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,954
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Trying to use Index-Match with criteria and between dates

    Quote Originally Posted by arossijr View Post
    Marcelo Branco, Quick Question... Formula is working but its not catching everything... I have a data entry that is on Prime Table but since it has a date of 6/18/2018 the formula does not pick it up... How can i correct that?
    Check if the dates are real dates (numbers), not text.

    M.

  10. #10
    Board Regular
    Join Date
    Aug 2016
    Location
    Toms River NJ
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to use Index-Match with criteria and between dates

    Marcelo,

    Is was my part number field, some of the numbers where not "numbers" I had to convert them via the green arrow in the left of the cell....

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •