# Nested IF(AND Statement - Index Match or Array?

#### AP1927

##### New Member
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

Column B
SCALE POINT CEILING

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:

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

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### AlanY

##### Well-known Member
you can use your lookup table like this

#### Indystick

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

Last edited:

#### AP1927

##### New Member
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

ABCDEF
2G110G213Not Due
3G215G430Due
4G320
5G425
6G530
7G635
8G740
9G845
10G950
11G1055
12G1160
13G1265
14G1370
15G1475
16G1580
17G1685
18G1790
19G1895
20G19100
21G20105
22G21110
23G22115
24G23120
25G24125
26G25130
27G26135
28G27140

</tbody>
Sheet1

Worksheet Formulas
CellFormula
F2=IF(VLOOKUP(D2,\$A\$2:\$B\$28,2,0)<E2,"Due","Not Due")

</tbody>

<tbody>
</tbody>

you're welcome

Replies
13
Views
270
Replies
9
Views
154
Replies
3
Views
67
Replies
14
Views
273
Replies
1
Views
166