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?

 

[StephenCrump]

How about:

	A	B	C	D	E	F
1	Rating	Compa-ratio	Increase
2	4.5	31%	4.55
3
4
5
6		0%	80%	90%	100%	120%
7	2.5
8	2.6
9	2.7
10	2.8
11	2.9
12	3.0
13	3.1
14	3.2
15	3.3
16	3.4
17	3.5
18	3.6
19	3.7
20	3.8
21	3.9
22	4.0
23	4.1
24	4.2
25	4.3
26	4.4	4.05	3.85	3.65	3.45	3.25
27	4.5	4.55	4.35	4.15	3.95	3.75
28	4.6
29	4.7
30	4.8
31	4.9
32	5.0
33
Sheet1

Cell Formulas
Range	Formula
C2	C2	=XLOOKUP(B2,B6:F6,XLOOKUP(A2,A7:A32,B7:F32),,-1)

 

[dsami0122]

I am getting an N/A
XLOOKUP(N3,'Salary Planning Matrix'!B1:F1,XLOOKUP(F3,'Salary Planning Matrix'!A2:A42,'Salary Planning Matrix'!B2:F42),,-1)

 

[Cubist]

What happens with just this?

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

 

[StephenCrump]

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

	A	B	C	D	E
1		Match		Match
2		4.5		4.5
3		#N/A		4.5
4		#N/A		21
5
6	1	2.5		2.5
7	2	2.6		2.6
8	3	2.7		2.7
9	4	2.8		2.8
10	5	2.9		2.9
11	6	3.0		3.0
12	7	3.1		3.1
13	8	3.2		3.2
14	9	3.3		3.3
15	10	3.4		3.4
16	11	3.5		3.5
17	12	3.6		3.6
18	13	3.7		3.7
19	14	3.8		3.8
20	15	3.9		3.9
21	16	4.0		4.0
22	17	4.1		4.1
23	18	4.2		4.2
24	19	4.3		4.3
25	20	4.4		4.4
26	21	4.5	TRUE	4.5	TRUE
27	22	4.6		4.6
28	23	4.7		4.7
29	24	4.8		4.8
30	25	4.9		4.9
31	26	5.0		5.0
32
Sheet1

Cell Formulas
Range	Formula
B3,D3	B3	=XLOOKUP(B2,B6#,B6#)
B4,D4	B4	=MATCH(B2,B6#,)
A6:A31	A6	=SEQUENCE(26)
B6:B31	B6	=SEQUENCE(26,,2.5,0.1)
D6:D31	D6	=ROUND(SEQUENCE(26,,2.5,0.1),1)
C26,E26	C26	=B26=4.5
Dynamic array formulas.

 

[dsami0122]

What happens with just this?

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

Still N/A

 

[Cubist]

Still N/A

That suggests 'Salary Planning Matrix'!B1:F1 are text and not numbers. Once you change it to numbers the formula @StephenCrump provided should work.

 

[dsami0122]

Says everything is a number

 

[StephenCrump]

That suggests 'Salary Planning Matrix'!B1:F1 are text and not numbers ...

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

 

[StephenCrump]

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