Nested IF(AND Statement - Index Match or Array?

AP1927

New Member
Joined
Nov 28, 2014
Messages
13
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
 

Some videos you may like

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
Joined
Oct 30, 2014
Messages
4,192
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you can use your lookup table like this

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">GRADE</td><td style=";">SCALE POINT CEILING</td><td style="text-align: right;;"></td><td style=";">GRADE</td><td style=";">Points</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">G1</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style=";">G2</td><td style="text-align: right;;">13</td><td style="background-color: #E2EFDA;;">Not Due</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">G2</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style=";">G4</td><td style="text-align: right;;">30</td><td style="background-color: #E2EFDA;;">Due</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">G3</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">G4</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">G5</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">G6</td><td style="text-align: right;;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">G7</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">G8</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">G9</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">G10</td><td style="text-align: right;;">55</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">G11</td><td style="text-align: right;;">60</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">G12</td><td style="text-align: right;;">65</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">G13</td><td style="text-align: right;;">70</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">G14</td><td style="text-align: right;;">75</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">G15</td><td style="text-align: right;;">80</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">G16</td><td style="text-align: right;;">85</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">G17</td><td style="text-align: right;;">90</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">G18</td><td style="text-align: right;;">95</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">G19</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">G20</td><td style="text-align: right;;">105</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">G21</td><td style="text-align: right;;">110</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">G22</td><td style="text-align: right;;">115</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">G23</td><td style="text-align: right;;">120</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">G24</td><td style="text-align: right;;">125</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style=";">G25</td><td style="text-align: right;;">130</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">G26</td><td style="text-align: right;;">135</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style=";">G27</td><td style="text-align: right;;">140</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=IF(<font color="Blue">VLOOKUP(<font color="Red">D2,$A$2:$B$28,2,0</font>)<E2,"Due","Not Due"</font>)</td></tr></tbody></table></td></tr></table><br />
 

Indystick

Board Regular
Joined
Mar 2, 2018
Messages
60
Nevermind! Someone beat me to the response. I like the proposed solution a lot.
 
Last edited:

AP1927

New Member
Joined
Nov 28, 2014
Messages
13
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
1GRADESCALE POINT CEILINGGRADEPoints
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

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

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,192
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,109,488
Messages
5,529,164
Members
409,852
Latest member
Perry123
Top