Spreadsheet: 144483d1330990704-using-multiple-conditions-in-vlookup-post-code-check
Sheet: Postcode check

Converted Exchange | Converted State | Converted Region | Converted Postcode | Toll Free | first digit | first 5 digits | prefix | terminating number | ? | date | time | duration | status | F1 | F2 | F3 | F4 | F5 | F6 | F7 | Exchange | State | Postcode
NYORA | VIC | Foster, Warragul, Drouin | ADAMS ESTATE | 1800xxxxxx | 3 | 35659 | **** | xxxxxx | 0 | 2012-02-27 00:00:00 | 15:07:16 | 00:00:10 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | NYORA | VIC | 3984
BURWOOD NSW | NSW | Sydney | ABBOTSFORD | 1800xxxxxx | 2 | 28741 | **** | xxxxxx | 0 | 2012-02-28 00:00:00 | 13:27:42 | 00:00:07 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | BURWOOD NSW | NSW | 2046
KILMORE | VIC | Wangaratta | FORBES | 1800xxxxxx | 3 | 35781 | **** | xxxxxx | 0 | 2012-02-29 00:00:00 | 08:56:13 | 1900-01-01 00:00:00 | Other | 0 | 1 | 0 | 0 | 0 | 0 | 1 | KILMORE | VIC | 3764
CIVIC | ACT | Canberra, Queanbeyan, Yass | n/a | 1800xxxxxx | 2 | 26243 | **** | xxxxxx | 0 | 2012-03-01 00:00:00 | 08:04:58 | 00:00:11 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | CIVIC | ACT | 2601
PRESTON VIC | VIC | Melbourne | GILBERTON | 1800xxxxxx | 3 | 39471 | **** | xxxxxx | 0 | 2012-03-01 00:00:00 | 18:56:10 | 00:00:15 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | PRESTON VIC | VIC | 3072
Mobile Orig | Mobile | Mobile | Mobile | 1800xxxxxx | ***** | 42298 | 422 | xxxxxx | 0 | 2012-03-03 00:00:00 | 15:27:08 | 00:00:14 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | Mobile Orig | *** | ****
GLENROY VIC | VIC | Melbourne | GLENROY | 1800xxxxxx | 3 | 39306 | **** | xxxxxx | 0 | 2012-02-27 00:00:00 | 15:56:03 | 00:05:41 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | GLENROY VIC | VIC | 3046
BOX HILL | VIC | Melbourne | BOX HILL NORTH | 1800xxxxxx | 3 | 39899 | **** | xxxxxx | 0 | 2012-02-27 00:00:00 | 20:53:56 | 00:00:01 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | BOX HILL | VIC | 3129
BOX HILL | VIC | Melbourne | BOX HILL NORTH | 1800xxxxxx | 3 | 39899 | **** | xxxxxx | 0 | 2012-02-27 00:00:00 | 20:56:29 | 00:00:19 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | BOX HILL | VIC | 3129
BOX HILL | VIC | Melbourne | BOX HILL NORTH | 1800xxxxxx | 3 | 39899 | **** | xxxxxx | 0 | 2012-02-27 00:00:00 | 20:58:28 | 00:06:21 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | BOX HILL | VIC | 3129
BOX HILL | VIC | Melbourne | BOX HILL NORTH | 1800xxxxxx | 3 | 39899 | **** | xxxxxx | 0 | 2012-02-28 00:00:00 | 14:58:31 | 00:05:26 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | BOX HILL | VIC | 3129
BOX HILL | VIC | Melbourne | BOX HILL NORTH | 1800xxxxxx | 3 | 39899 | **** | xxxxxx | 0 | 2012-02-28 00:00:00 | 15:04:25 | 00:02:58 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | BOX HILL | VIC | 3129
Mobile Orig | Mobile | Mobile | Mobile | 1800xxxxxx | ***** | 42128 | 421 | xxxxxx | 0 | 2012-02-28 00:00:00 | 15:09:18 | 00:06:18 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | Mobile Orig | *** | ****
OXENFORD | QLD | Gold Coast, Beaudesert | GUANABA | 1800xxxxxx | 7 | 75573 | **** | xxxxxx | 0 | 2012-02-29 00:00:00 | 18:49:12 | 00:07:32 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | OXENFORD | QLD | 4210
Mobile Orig | Mobile | Mobile | Mobile | 1800xxxxxx | ***** | 42128 | 421 | xxxxxx | 0 | 2012-03-01 00:00:00 | 08:18:42 | 00:06:27 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | Mobile Orig | *** | ****
Mobile Orig | Mobile | Mobile | Mobile | 1800xxxxxx | ***** | 40918 | 409 | xxxxxx | 0 | 2012-02-28 00:00:00 | 07:22:53 | 00:00:28 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | Mobile Orig | *** | ****
Mobile Orig | Mobile | Mobile | Mobile | 1800xxxxxx | ***** | 40918 | 409 | xxxxxx | 0 | 2012-02-28 00:00:00 | 07:23:37 | 00:00:58 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | Mobile Orig | *** | ****
Mobile Orig | Mobile | Mobile | Mobile | 1800xxxxxx | ***** | 40918 | 409 | xxxxxx | 0 | 2012-02-28 00:00:00 | 07:24:46 | 00:04:41 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | Mobile Orig | *** | ****
OLD BAR | NSW | Port Macquarie, [NAME], Taree, Lord [NAME], [NAME]. | BLACK HEAD | 1800xxxxxx | 2 | 26557 | **** | xxxxxx | 0 | 2012-03-01 00:00:00 | 07:28:39 | 00:06:51 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | OLD BAR | NSW | 2430
Unknown Exchange | ******************** | Unknown Region | Mobile | 1800xxxxxx | ***** | 89762 | **** | xxxxxx | 0 | 2012-02-27 00:00:00 | 10:11:27 | 00:15:32 | Answered | 1 | 0 | 0 | 0 | 0 | 0 | 0 | ******************** | *** | ****
Issue #1
Row highlighted in yellow is not from a mobile.  The ******************** in column V (and fact that col G says it is from 08 9762 xxxx) indicates that it is from an unknown exchange.
I need to modify column D formula to first check for **** in col X, then check for ******************** in column D (or check for digits in col G = 4????).
If both conditions true, I would return 'unkhown postcode' in column D
Issue #2
I need to modify column B formula to first check for *** in col W, then check for ******************** in column D (or check for digits in col G = 4????).
If both conditions true, I need to do a VLOOKUP and return the state from column C on 'Post Code' worksheet
N.B.  Post code sheet is normally over 16000 rows.