Find label value in a pivot table at a certain point in a % running total in

[bbankston]

I'm struggling to come up with a formula to accomplish what I need to do. I have a pivot table with % running total in an age series of results. I want to extract the age at which the % running total >= x%, in the example 60%. In the example image of my data, I want to return and use the value 8 found in the row lables based on the hightlighted cell value for the % running total in. Ideas?

 

[C Moore]

If you have Office 365 version xlookup will do it. Combine it with dynamic named ranges and it'd be pretty straightforward.

 

[bbankston]

If you have Office 365 version xlookup will do it. Combine it with dynamic named ranges and it'd be pretty straightforward.

I'm using office 2019. So, unfortunately, xlookup isn't available.

 

[C Moore]

Index/match.

=INDEX(Row labels,MATCH(value,Location,1)+1)

Since its a running cumulative it's an ascending order. The 1 in the match finds the closest or equal that's less than. The +1 for row makes it the next value. This will be off by one row if an exact match exists.

 

[C Moore]

So if statement, duh, sorry.

=INDEX(Row labels, IFERROR(MATCH(value,Location,0),MATCH(value,Location,1)+1))

 

[bbankston]

Index/match.

=INDEX(Row labels,MATCH(value,Location,1)+1)

Since its a running cumulative it's an ascending order. The 1 in the match finds the closest or equal that's less than. The +1 for row makes it the next value. This will be off by one row if an exact match exists.

I need to be able to find the match value. The match value is the minimun % running total value that is >= .6. The problem I run into using the match function is that I really need to use the -1 match type but I can't order the results in descending order because of the nature of % running total in. I cannot use match type 1 because I need the min value greater than my threshold value, not the max value less than the threshhold. The next percentage step of from my threshold is unpredictable so I can't set an upper value that will get me the right row. I appreciate your help.

 

[C Moore]

I think the formula I submitted solves that for you. By making it MATCH(value,Location,1)+1, it finds the one less and then offsets it by 1.

 

[bbankston]

I think the formula I submitted solves that for you. By making it MATCH(value,Location,1)+1, it finds the one less and then offsets it by 1.

ah, yep, sorry. Missed the index offset. Pretty good solution. Appreciate it!