Excel Multi Level If Than with Greater Than and Less Than needs

[dsami0122]

We have two tables that shows titles and what the range of salary is for that specific title it has MIN, MID, MAX range,

What I am trying to calculate is what the compa-ratio is for the level first then I have to calculate a multiple level If statement to pull over the Merit increase from this table

First one is if Performance Rating from First Table F Column is Equal to the Second Table A Column, And the Ration from first table column N meet the criteria of any the columns in the second table it will pull over the correct percentage.

So in the SAMPLE CASE it would pull over the below 80% and Equal to 4.5 performance rating and increase would be 4.55%

Does this make sense?

 

[Cubist]

I don't think so. You need match_mode = -1 for this XLOOKUP.

I accidentally removed the "-1" when copying your formula.

@dsami0122
Try if this returns N/A?

=XLOOKUP(N3,'Salary Planning Matrix'!B1:F1,'Salary Planning Matrix'!B1:F1,,-1)

 

[dsami0122]

I suggest you also have a look at how you're generating your performance rating column. Compare:

I am not quite sure the formula your suggesting

@dsami0122, try over-typing your performance rating 4.5 with the number 4.5.

just over typed didnt change anything

 

[StephenCrump]

just over typed didnt change anything

Try doing it in both places the 4.5 appears.

 

[dsami0122]

I am not quite sure the formula your suggesting

just over typed didnt change anything

I just copied and pasted to a new tab and now its working - thank you all

 

[StephenCrump]

That's good. Not sure why it wasn't working before, but glad we could help.

 

[dsami0122]

Just added another testing line and getting N/A again on line two if I type 4.50 over the 4.30 it works but no other numbers now - this is crazy

 

[Cubist]

Did you lock the references?

=XLOOKUP(N3,'Salary Planning Matrix'!$B$1:$F$1,XLOOKUP(F3,'Salary Planning Matrix'!$A$2:$A$42,'Salary Planning Matrix'!$B$2:$F$42),,-1)

 

[dsami0122]

Did you lock the references?

=XLOOKUP(N3,'Salary Planning Matrix'!$B$1:$F$1,XLOOKUP(F3,'Salary Planning Matrix'!$A$2:$A$42,'Salary Planning Matrix'!$B$2:$F$42),,-1)

I did yes

 

[dsami0122]

I did yes

I even added the $ so that it carried down a line properly

 

[Cubist]

Try in row 3 and copy down.

=XLOOKUP(N3,'Salary Planning Matrix'!$B$1:$F$1,XLOOKUP(F3,'Salary Planning Matrix'!$A$2:$A$42,'Salary Planning Matrix'!$B$2:$F$42,,-1),,-1)