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?

 

[dsami0122]

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)

TADA - OMG THANK YOU

 

[dsami0122]

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)

TADA Yes that worked - THANK YOU

 

[Alex Blakenburg]

TADA Yes that worked - THANK YOU

You need to be aware of the ramifications of that.
You may be seeing 4.3 in column F and returning the value from the line with 4.2 from the Salary planning table.
It seems likely that either column F is a formula or Salary column A is a formula or Both.
They are displaying 2 decimals but are not actually 2 decimals.
Ideally the formula in column F and column A would have rounding applied to them eg =Round(formula,2)

You could also do it at the formula level but it will make the calculation work harder.

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

 

[Cubist]

@Alex Blakenburg
If you review the first picture post #16. You can see the OP is trying to look up 3.25 which is not on the table.

 

[Alex Blakenburg]

@Cubist Good point, I had not picked up on that.
The rounding might still be an issue but you are right that it needs the "-1" you included (find exact match or next smaller item)