Spreadsheet: 715899d1611911534-need-help-with-a-vlook-up-if-statement-matrix-perf-worksheet-ans
Sheet: Sheet1

Pretend Information |  |  |  |  | Default will not be displayed in [NAME] module |  |  |  | Lookup Table
Emp ID | LTIP Levels | [NAME] | [NAME] | Min (2) | Default (3) | [NAME] (4) | Amount |  | LTIP Levels | [NAME] | [NAME] | Min (2) | Default (3) | [NAME] (4) | Category | Amount
A | Director | GEO A | 4 |  |  |  |  |  | Sr Director | GEO A | 4 | 68500 | 88000 | 110000 | [NAME] | 110000
B | Director | GEO A | 3 |  |  |  |  |  | Sr Director | GEO A | 3 | 68500 | 88000 | 110000 | Default | 88000
C | Sr Director | GEO A | 2 |  |  |  |  |  | Sr Director | GEO A | 2 | 68500 | 88000 | 110000 | Min | 68500
D | Sr Director | GEO B | 4 |  |  |  |  |  | Director | GEO A | 4 | 41500 | 52000 | 65000 | [NAME] | 65000
E | Director | GEO B | 3 |  |  |  |  |  | Director | GEO A | 3 | 41500 | 52000 | 65000 | Default | 52000
F | Director | GEO B | 2 |  |  |  |  |  | Director | GEO A | 2 | 41500 | 52000 | 65000 | Min | 41500
G | Director | GEO A | 4 |  |  |  |  |  | Sr Mgr & Principal IC | GEO A | 4 | 29500 | 36500 | 46000 | [NAME] | 46000
H | Sr Director | GEO A | 3 |  |  |  |  |  | Sr Mgr & Principal IC | GEO A | 3 | 29500 | 36500 | 46000 | Default | 36500
I | Director | GEO A | 2 |  |  |  |  |  | Sr Mgr & Principal IC | GEO A | 2 | 29500 | 36500 | 46000 | Min | 29500
J | Sr Director | GEO B | 4 |  |  |  |  |  | Mgr & Principal IC | GEO A | 4 | 21500 | 27500 | 34000 | [NAME] | 34000
K | Director | GEO B | 3 |  |  |  |  |  | Mgr & Principal IC | GEO A | 3 | 21500 | 27500 | 34000 | Default | 27500
L | Mgr & Principal IC | GEO B | 2 |  |  |  |  |  | Mgr & Principal IC | GEO A | 2 | 21500 | 27500 | 34000 | Min | 21500
M | Sr Mgr & Principal IC | GEO B | 4 |  |  |  |  |  | Sr Director | GEO B | 4 | 57000 | 74500 | 93000 | [NAME] | 93000
** For New Hires, to give guidance that award should be at Min ** |  |  |  |  |  |  |  |  | Sr Director | GEO B | 3 | 57000 | 74500 | 93000 | Default | 74500
 |  |  |  | Not to display value in column F |  |  |  |  | Sr Director | GEO B | 2 | 57000 | 74500 | 93000 | Min | 57000
Query Rule for Min, [NAME] and [NAME] |  |  |  | F2:F15 Custom format--> ;;; |  |  |  |  | Director | GEO B | 4 | 33500 | 43500 | 55500 | [NAME] | 55500
 | Column E | Column G | Column H | H2 is changed |  |  |  |  | Director | GEO B | 3 | 33500 | 43500 | 55500 | Default | 43500
 | Table Range = Min and [NAME] |  |  |  |  |  |  |  | Director | GEO B | 2 | 33500 | 43500 | 55500 | Min | 33500
[NAME] | Query Table-Min | Query Table-Max | Query Table - Rec |  |  |  |  |  | Sr Mgr & Principal IC | GEO B | 4 | 22500 | 27500 | 34500 | [NAME] | 34500
4 | Mid | [NAME] | [NAME] |  |  |  |  |  | Sr Mgr & Principal IC | GEO B | 3 | 22500 | 27500 | 34500 | Default | 27500
3 | Min | [NAME] | Mid |  |  |  |  |  | Sr Mgr & Principal IC | GEO B | 2 | 22500 | 27500 | 34500 | Min | 22500
2 | Min | Min | Min |  |  |  |  |  | Mgr & Principal IC | GEO B | 4 | 15500 | 20500 | 26000 | [NAME] | 26000
1 | 0 | 0 | 0 | Need to watch for 1's and manually put 0 amounts |  |  |  |  | Mgr & Principal IC | GEO B | 3 | 15500 | 20500 | 26000 | Default | 20500
New Hire | Mid | Mid | Min | Need to identify those hired in 2020 but BEFORE 11/1/20 |  |  |  |  | Mgr & Principal IC | GEO B | 2 | 15500 | 20500 | 26000 | Min | 15500
New Hire | N/A | N/A | N/A | Need to identify those hired in 2020 but AFTER 11/1/20
CY2021 Guidelines
Job Level |  | Low | Medium | High
GEO A
Sr Director-M6 & P8 |  | 68500 | 88000 | 110000
Director-M5 & P7 |  | 41500 | 52000 | 65000
Sr.Mgr & Principal-M4 & P6 |  | 29500 | 36500 | 46000
Mgr & Principal-M3 & P5 |  | 21500 | 27500 | 34000
GEO B
Sr Director-M6 & P8 |  | 57000 | 74500 | 93000
Director-M5 & P7 |  | 33500 | 43500 | 55500
Sr.Mgr & Principal-M4 & P6 |  | 22500 | 27500 | 34500
Mgr & Principal-M3 & P5 |  | 15500 | 20500 | 26000