+ Reply to Thread
Results 1 to 5 of 5

Changing INDEX Reference based upon a certain value

  1. #1
    Registered User
    Join Date
    09-23-2022
    Location
    Arkansas
    MS-Off Ver
    Office 2019
    Posts
    6

    Changing INDEX Reference based upon a certain value

    Hi all- first time posting on here. I'm in a new role that requires much more Excel use than I'm used to and very excited to comb the forums to learn more!

    I have a retail sales data set where I'm looking at the customer purchase quantity (e.g., customer purchased 2 units in a transaction) for a large number of different items.

    I'm trying to identify the first number > 75% under the cumulative units column (Column H) , then return the associated customer purchase quantity (column B). I can do this (formula is listed in column M), but I need to adjust the INDEX array to only consider rows associated with a specific item number (e.g., item 500 should be limited to rows 2 to 8).

    The formula for item #600 should return "6", which is the customer purchase qty associated with the first cumulative units % > 75%.

    How can I adjust the function so only rows associated with item number 600 are considered?

    Thank you for the help!! i'm very excited to leverage this forum to build my excel skillset.
    Attached Images Attached Images

  2. #2
    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
    43,893

    Re: Changing INDEX Reference based upon a certain value

    Next time, don't give us a non-editable picture... give us an Excel file.

    =INDEX(B:B,AGGREGATE(15,6,ROW($H$2:$H$20)/(($A$2:$A$20=J2)*($H$2:$H$20>0.75)),1))

    copied down.

    See file
    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

  3. #3
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: Changing INDEX Reference based upon a certain value

    Hi tw333,
    welcome to the forum.
    Maybe this is the formula you are looking for:
    Please Login or Register  to view this content.
    Of course you can go much higher than mentioned range

    Cheers
    Eastw00d
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  4. #4
    Registered User
    Join Date
    09-23-2022
    Location
    Arkansas
    MS-Off Ver
    Office 2019
    Posts
    6

    Re: Changing INDEX Reference based upon a certain value

    Quote Originally Posted by Glenn Kennedy View Post
    Next time, don't give us a non-editable picture... give us an Excel file.

    =INDEX(B:B,AGGREGATE(15,6,ROW($H$2:$H$20)/(($A$2:$A$20=J2)*($H$2:$H$20>0.75)),1))

    copied down.

    See file
    Quote Originally Posted by Eastw00d View Post
    Hi tw333,
    welcome to the forum.
    Maybe this is the formula you are looking for:
    Please Login or Register  to view this content.
    Of course you can go much higher than mentioned range

    Cheers
    Eastw00d
    I'll be sure link the actual file next time but this is incredible, both of these work. You guys are amazing. Thank you!!
    Last edited by tw333; 09-25-2022 at 03:39 PM.

  5. #5
    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
    43,893

    Re: Changing INDEX Reference based upon a certain value

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

+ 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. Changing multiple PivotTables based on text string/ Cell reference
    By NicolaiKC in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-31-2021, 09:42 AM
  2. [SOLVED] Copying a Formula over multiple rows without changing the INDEX MATCH cell reference.
    By nathandavies9 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-16-2017, 07:57 AM
  3. [SOLVED] insert formulas based off changing reference cells
    By rossg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-06-2014, 11:47 PM
  4. Changing reference sheet based on current quarter?
    By GarnetBlack32 in forum Excel General
    Replies: 2
    Last Post: 02-05-2012, 05:19 PM
  5. Changing Sheet Names based on a Cell Reference
    By tjloggia in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-04-2012, 04:35 PM
  6. changing conditional formatting based on a reference
    By altsean06 in forum Excel General
    Replies: 1
    Last Post: 05-12-2011, 06:17 PM
  7. Replies: 2
    Last Post: 07-14-2006, 12:25 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