RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 790
- Office Version
- 365
- Platform
- Windows
I have this badboy of a formula:
=INDEX(Dashboard!$F$4:$F$5069,MATCH(1,INDEX((LARGE(IF(Dashboard!$A$4:$A$5069=A28,Dashboard!$T$4:$T$5069),1)=Dashboard!$T$4:$T$5069)*(Dashboard!$A$4:$A$5069=A28),),0))
Ignoring the ins-and-outs of it, it does an Index:Match lookup on the Largest value of a range, IF that range matches a criteria.
You see that bit I bolded+underlined? That tells it to grab the 1st largest (or the best rank value)
I would like a button to press that swaps that integer for the next highest, and another button that swaps it for the next lowest. 1 becomes 2, or if pressed again it becomes 3, or 4... And vice versa.
Is this possible?
Because of the large range of cells this formula is in (300 or so) I can't change the RIGHT(64) character, for instance, as the lookup cell is in either A4 or A54 or A305 so it'll push the right cell out.
Any ideas guys? Thanks.
=INDEX(Dashboard!$F$4:$F$5069,MATCH(1,INDEX((LARGE(IF(Dashboard!$A$4:$A$5069=A28,Dashboard!$T$4:$T$5069),1)=Dashboard!$T$4:$T$5069)*(Dashboard!$A$4:$A$5069=A28),),0))
Ignoring the ins-and-outs of it, it does an Index:Match lookup on the Largest value of a range, IF that range matches a criteria.
You see that bit I bolded+underlined? That tells it to grab the 1st largest (or the best rank value)
I would like a button to press that swaps that integer for the next highest, and another button that swaps it for the next lowest. 1 becomes 2, or if pressed again it becomes 3, or 4... And vice versa.
Is this possible?
Because of the large range of cells this formula is in (300 or so) I can't change the RIGHT(64) character, for instance, as the lookup cell is in either A4 or A54 or A305 so it'll push the right cell out.
Any ideas guys? Thanks.