+ Reply to Thread
Results 1 to 13 of 13

index return data value between two dates and additional criteria

  1. #1
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    index return data value between two dates and additional criteria

    I want to extract the transactions from column H that match the specific product in column J, but at the same time, it should match location 65 location data is in column A the attached picture show the full data details.

    currently I'm using =INDEX($H:$H,MATCH(1,(($B:$B=J2)($D:$D>=$K$3)($D:$D<=$L$3)),0))

    how can I expand the above index formula to include the specification of location 65

    the formula should return the transactions that were made between two dates k3 start date and L3 end date for each product line in the J column and also those transactions should be made by 65 location from the data given at column A

    I really need your help on this I have been looking for a resolution to my request.index-match-multiple-criteria.pngBook1.xlsx

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: index return data value between two dates and additional criteria

    any expected example in your attachment?
    Last edited by sandy666; 10-22-2022 at 04:22 PM.

  3. #3
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    Re: index return data value between two dates and additional criteria

    I was trying to use the following formula, but it also didn't give me the result I'm looking for as I explained earlier.

    {=INDEX($H:$H,MATCH(1,(($B:$B=J2)*($A:$A=L1)*($D:$D>=$K$3)*($D:$D<=$L$3)),0))}

    I hope to find the correct formula that can give me the desired result

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: index return data value between two dates and additional criteria

    basic question from me: are you still using XL2010 ?

    >>. are you filtering main table by date range and Product Code ?
    >> what means 65 ? is this a result of filtering above?

    example:

    TAREGT LOCATION
    24
    Product TRANS.
    START DATE END DATE BEA2302 400650200000576
    08/10/2022
    08/10/2022
    BOD1093 400950300001261
    BV1050 401350300000754
    DAZ1065 400950200000911
    DAZ1068 400950200000911
    FEF1119 400950200000912
    FEF1144 401150100000827
    FEF1144 401350200000747
    HAL1086 401350100000080
    HAL1086 400950200000908
    HAL1086 400950300001264
    HAL1088 400950200000908
    HAL1088 400950300001264
    HAL1089 400950300001264
    HAL1091 400950200000908
    HAL1091 400950300001264
    HAL1098 401350200000750
    HAL9242 401350300000756
    HAL9242 400950200000910
    HAL9242 401350300000755
    HAL9243 401350300000756
    HAL9243 401350300000755
    HAL9244 401350300000755
    HAL9244 401350300000756
    Last edited by sandy666; 10-22-2022 at 05:43 PM.

  5. #5
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    Re: index return data value between two dates and additional criteria

    Thank you for your respond
    I’m using Microsoft XL 2016

    Yes, I’m trying to use the index formula to extract transactions from column H that match the products code in column J between the start & end date range. and 65 is the store code that I'm targeting to add to the filtering formula as well.

    In the given attachment I was able to extract the transactions
    Versus each product within the start & end date range. but I don't know how to add additional filtering to that formula considering 65 store code "65" as the targeted filter also.

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: index return data value between two dates and additional criteria

    first: update your profile about excel version/name: 2016

    XL2016 contain Power Query so you can use it for your problem
    here is solution without any formula but with PQ only

  7. #7
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    Re: index return data value between two dates and additional criteria

    @sandy666 thank you for your quick response I really appreciate your assistance, I'm actually more interested and targeting a formula that will get me the result I'm looking for, as I explained earlier, index, match format works better for me because this worksheet is connected to other workout and I want it to work in that way.

    I look forward to a formula that will get me the same result if possible.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: index return data value between two dates and additional criteria

    Please update your forum profile to Excel 2016 - do this NOW.

    Although horribly slow, try this:

    {=INDEX($H:$H,MATCH(1,(($B:$B=J2)*(--$A:$A=$L$1)*($D:$D>=$K$3)*($D:$D<=$L$3)),0))}
    Last edited by AliGW; 10-23-2022 at 02:29 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: index return data value between two dates and additional criteria

    Quote Originally Posted by MAJID1479 View Post
    I look forward to a formula that will get me the same result if possible.
    no problem
    have a nice day

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: index return data value between two dates and additional criteria

    Please see post #8 (in case you missed it).

  11. #11
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    Re: index return data value between two dates and additional criteria

    @AliGW Thank you for your help, the below formula worked perfectly, yes its slow, but its okay and its so helpful.
    {=INDEX($H:$H,MATCH(1,(($B:$B=J2)*(--$A:$A=$L$1)*($D:$D>=$K$3)*($D:$D<=$L$3)),0))}

    with regards to the forum profile update to Excel 2016, I already did it and attached the screenshot just for reference, and still, Power Query is not working with my excel version. I'm really interested to know more about it and how can I have that option with my XL version.

    Attachment 801945Attachment 8019461.png

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: index return data value between two dates and additional criteria

    No, you have NOT updated your profile. Do it NOW!!!

    Glad to have helped.

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    Attached Images Attached Images

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: index return data value between two dates and additional criteria

    Thank you for updating your profile at last.

    You already have PowerQuery: https://www.microsoft.com/en-us/micr...in-excel-2016/

    As explained in post #12, please mark this thread as solved, etc.

+ 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. [SOLVED] COUNTIFS between two dates over multiple columns with additional criteria
    By Knawl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-22-2021, 11:31 AM
  2. [SOLVED] Index match with multiple criteria with additional task-Please help.
    By farniajr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-23-2018, 03:43 AM
  3. [SOLVED] Lookup a value within a range with 2 additional criteria, return a value
    By gaker10 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-12-2018, 08:17 PM
  4. INDEX/MATCH to return row data if between two dates
    By iantix in forum Excel Formulas & Functions
    Replies: 35
    Last Post: 12-15-2016, 01:52 PM
  5. [SOLVED] Sumif between a range of dates plus one additional criteria
    By caliskier in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-28-2014, 06:03 PM
  6. How to insert additional matching criteria to Index
    By erumhyder in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-26-2011, 02:07 AM
  7. Sum Data Using Dates and Additional Criteria
    By kgibson20 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-29-2008, 04:59 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