Nested IF(AND Statement - Index Match or Array?

[AP1927]

Hi,

I am trying to work out who in my data is eligible for a pay increment. I have a 2 column lookup table $A$2:$B$28 that shows Grade in Column A and Scale Point Ceiling in Column B
e.g.
Column A
GRADE

Column B
SCALE POINT CEILING
Administrator 10

I have thousands of employees and so doing individual IF & AND statements will take forever.

The IF Statement in my employee worksheet current works and is:

=IF(AND(I3<10,H3="Administrator"),"Due","Not Due")

So, if I3 is below 10 Scale Point then "Due" otherwise "Not Due".

I have thousands of people 20 plus grades, what is the best way to calculate this? I want to use my lookup table array $A$2:$B$28 on the other
worksheet as an IF & AND Array formula?

Any help will be much appreciated.

Andy

 

[AlanY]

you can use your lookup table like this

Book1
	A	B	C	D	E	F
1	GRADE	SCALE POINT CEILING		GRADE	Points
2	G1	10		G2	13	Not Due
3	G2	15		G4	30	Due
4	G3	20
5	G4	25
6	G5	30
7	G6	35
8	G7	40
9	G8	45
10	G9	50
11	G10	55
12	G11	60
13	G12	65
14	G13	70
15	G14	75
16	G15	80
17	G16	85
18	G17	90
19	G18	95
20	G19	100
21	G20	105
22	G21	110
23	G22	115
24	G23	120
25	G24	125
26	G25	130
27	G26	135
28	G27	140
Sheet1

Cell Formulas
Range	Formula
F2	=IF(VLOOKUP(D2,$A$2:$B$28,2,0))

 

[Indystick]

Nevermind! Someone beat me to the response. I like the proposed solution a lot.

 

[AP1927]

Thanks for this, I had to actually tweak the formula because some ceiling points are higher than whats in the lookup table.
The formula works and looks like this:

=IF(OR(VLOOKUP(H3,'Scale Points'!$A$2:$B$28,2,0)=I3,VLOOKUP(H3,'Scale Points'!$A$2:$B$28,2,0)<I3),"Not Due","Due")

Much appreciated AlanY!

you can use your lookup table like this

	A	B	C	D	E	F
1	GRADE	SCALE POINT CEILING		GRADE	Points
2	G1	10		G2	13	Not Due
3	G2	15		G4	30	Due
4	G3	20
5	G4	25
6	G5	30
7	G6	35
8	G7	40
9	G8	45
10	G9	50
11	G10	55
12	G11	60
13	G12	65
14	G13	70
15	G14	75
16	G15	80
17	G16	85
18	G17	90
19	G18	95
20	G19	100
21	G20	105
22	G21	110
23	G22	115
24	G23	120
25	G24	125
26	G25	130
27	G26	135
28	G27	140

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F2 =IF(VLOOKUP(D2,$A$2:$B$28,2,0)<E2,"Due","Not Due") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[AlanY]

you're welcome