Hi all,
I am need of some help with creating a formula in order to search 2 columns (Columns A and C) for matching data and bring back a value from a third column (Column B). Essentially columns A and B are one table and column C is by itself.
Please find below an extract of data;

A B C D
SUPPLIER NAME SUPPLIER CODE NARRATION REQUIRED OUTCOME (by formula)
1 AVIS AUSTRALIA 04321 Purchase BestBuys.com
2 DE NEEFE SIGNS 04407 Purchase Whitcoulls Gallery
3 RICOH AUSTRALIA 04441 Purchase (NZD 17.36) Whitcoulls Internet Store
4 ZALLCOM 04444 Purchase Legato Espresso Cafe
5 LEASE PLAN 04567 Purchase The Stamford Plaza Sydney 04660
6 STAMFORD PLAZA 04660 Purchase The Stamford Plaza Sydney 04660
7 DARWIN CITY COUNCIL 04737 Purchase The Stamford Plaza Sydney 04660
8 TOSHIBA AUSTRALIA PTY LTD 04802 Purchase Profile Publishing
9 COMPLETE OFFICE SUPPLIES 04823 Purchase Gecko Printing
10 THOMSON REUTERS 04973 Purchase Stamford Sydney Airport Hotel 05436
11 STAMFORD SYDNEY AIRPORT HOTEL 05436 Purchase Kwik
12 TEMPORARY FENCING PTY LTD 05494 Purchase (15.65USD) Ricoh Australia
13 DEPT FOREIGN AFFAIRS & TRADE 05621
14 KWIK COPY PRINTING 05675
15 ADELAIDE CITY COUNCIL 05830
16 FOUR SEASONS HOTEL SYDNEY 05934
17 SOUND ADVICE 06233
18 CREDIT CARD – ONE OFF CREDITORS 99999



So what I am trying to achieve is;
I want for each instance of a particular supplier referenced in column C, for a search to be done on column A, and for the corresponding supplier code in column B to be brought back to column D.
So for example, for ‘Purchase The Stamford Plaza Sydney’ (cell reference C5,C6, and C7) I would like the formula to search column A, find ‘Stamford Plaza’ (cell reference =A6) and place in the relevant cells (cell references = D5, D6 and D7) in column D, supplier code 04660.
For narrations that do not have a corresponding supplier name in column A, the ‘CREDIT CARD – ONE OF CREDITORS’ supplier should be used, supplier code 99999, e.g. Purchase Whitcoulls Gallery (Cell reference = C2), would produce in D2 99999.
Columns A and B are a listing out of the finance system and therefore has potentially thousands of values, column C is out of a credit card system and potentially has hundreds of values.
I have tried using various formulas incorporating wildcards in IF statements, COUNTIF, VLOOKUPS….
I would be really appreciative of any advice or outcomes that can be offered.
Thank you in advance 
:-)

Cheers,
Katie