• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup rows until reached value

Tommy24

New Member
Hello,
I'm new here so maybe not aware of every rules but I'm blocked on a formula in order to lookup several rows until reaching a certain amount and drag it.
Is someone already faced this kind of formula ?
I attached you a file who will be maybe more explicit.
Many thanks
 

Attachments

  • Lookup row until x value.xlsx
    10.5 KB · Views: 15
Power Query offering in attached. See result table at cell J2.
Right-click it and choose Refresh to update it as you would a pivot table.
Change the extent of the table at cell D2 to include some more sample rows; one with apple and the other a mis-spelling to see what happens.
The sort order of the table at cell A2 doesn't matter, the query does its own sorting.
One anomaly is if you have 0 for the amount in the Real Impact table it still returns one row from the first table.
 

Attachments

  • Chandoo48851Lookup row until x value.xlsx
    21.1 KB · Views: 9
Hello p45cal
Sorry to reply so late, your code works well but it's very complicated for me to adapt it to the real file I have.

Do you know if there is a way to find the same result with formulas only, avoiding Power Query ?

Thanks
 
Try this with key combination of Ctrl + Shift + Enter
=LARGE(($A$3:$A$18=G3)*$B$3:$B$18,COUNTIF($G$3:G3,G3))
 
Hi to all!
Another option (With Microsoft 365) could be:
PHP:
=LET(o,SORT(A3:B18,{1;2},{1;-1}),s,"|",
f,LAMBDA(t,p,TEXTBEFORE(t,p)),g,LAMBDA(t,p,TEXTAFTER(t,p)),n,INDEX(o,,1),
a,--MAP(SCAN("|0",n&s&INDEX(o,,2),LAMBDA(i,x,f(x,s)&s&(f(i,s)=f(x,s))*g(i,s)+g(x,s))),LAMBDA(n,g(n,s))),
r,MAP(n,LAMBDA(x,LET(v,FILTER(a,n=x),XLOOKUP(SUMIF(D3:D4,x,E3:E4),v,v,,1)))),
VSTACK(A2:B2,FILTER(o,(a<=r)*COUNTIF(D3:D4,n))))
Check file. Blessings!
 

Attachments

  • Lookup row until x value.xlsx
    11.5 KB · Views: 7
Hello Everybody,

Here is the file I would like to adapt

Many thanks
 

Attachments

  • Lookup row until XX value.xlsx
    520.7 KB · Views: 6
Can you give the password to unprotect the sheet. I can't place a table even on unlocked cells.
So far I can get this:
81148

Also I can't change your pivot table to repeat the headers in the Account column which would help with a formula solution.
ps. would a user defined function be OK? it would require the file to be macro-enabled.
 
You told me the password to unprotect that sheet in a private converstion, which worked. The attached has that sheet that I protected still with the same password.
In the attached is the solution I pictured in my last message. It's a Power Query solution and does NOT use the pivot table om Sheet1, but instead refers to the file:
W:\08_OTC\01_AR_Collections\2021\1. Off-Trade\1. Dashboard OD.CL\BE Offtrade 29-09-2022.xlsx
on your system.
Check:
1. It's more or less correct (you'll need to right-click somewhere on the result table (at cell J18 of Sheet1)) to update the information from your system (at the moment it's showing data from my system). If it is then I'll need to make it a bit more user friendly (eg. for determining the file you want to get info from, and for filtering that data)
2. You're happy for this to be a Power Query solution (no macro code needed (except to make it a lot more user-friendly))

Otherwise I'll go about writing a macro-only user-defined function. I'll wait to hear first.
 

Attachments

  • Chandoo48851Lookup row until XX value.xlsx
    532.9 KB · Views: 11
Hello
It works well, I adapted it to my files and I am very happy with it, no need a macro. Many thanks for your great help!
 
Good. Be aware that the query filters out account no. 30062069 as that is what your pivot table did.
 
Indeed, I checked all the filters and they remained as they was in the pivot, so it's exactly what I was looking for.

Thanks again !
 
Back
Top