Formulas needed to find 1) # and 2) Round Up

[mrb89]

Hi,

I'm trying to add a function to the following formula

=INDEX('Income Rankings 2023-2024'!A5:A105,MATCH(M27,INDEX('Income Rankings 2023-2024'!B5:P105,,MATCH(M25,'Income Rankings 2023-2024'!B4:P4,0)),1))

M25 is Family size
M27 is Income

I know that If a family of 3 makes $1147.52, the ranking should be ranked at # 13. Because the family makes more than $1133 (rank 12), the family falls under Rank #13.





Please note that there are 2 tabs 1) Income Rankings 2) Income Calculator - that's why the formula shoes "Income Rankings"

 

[Cubist]

Maybe this. Not tested.

=MATCH(M27,INDEX('Income Rankings 2023-2024'!B5:P105,0,MATCH(M25,'Income Rankings 2023-2024'!B4:P4,0)),1)+1

 

[Alex Blakenburg]

Maybe:
(the - 0.00001, is because the +1 isn't valid for an exact match so I needed to clock an exact match back 1)
With Match if you want the next greatest the list needs to be sorted descending, so this is a workaround.

=INDEX('Income Rankings 2023-2024'!$A$4:$A$105,
                  MATCH(M27-0.00001,
                                     INDEX('Income Rankings 2023-2024'!$B$4:$P$105,
                                                     0,
                                                    MATCH(M25,'Income Rankings 2023-2024'!B4:P4,0)),
                                      1)+1)