Help!! Formula to match text and bring back a related value required! Please Help!!

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

Hi FDibbins,
Thanks for the advice.... first time and all....

Please find attached a file with data as previously discussed. I am trying to determine the correct formula to result in the column D values (in yellow). These should be drawn from column B, based on the matching of columns A and C. Does this make sense?

As mentioned earlier, columns A and B are from a financial systems Accounts Payable chart and could potentially have thousands of records, and Column C is from a separate credit card system and could potentially have hundreds of transactions/lines.

PS I hope the attachment of the file has worked...

Thank you again in advance.
Attachment 421178

Hi guys,
Thanks for your input. What if the narration column only stated, the supplier name or part there of.... and a substitute formula was entered over the top to eradicate instances of "The" at the beginning?

Is there no way for a wildcard to find matching text within the string? Even if it is not complete ie. find "Kwik" within "Kwik Copy Printing " to bring back "5675".

Outside of this, have you got any suggestions as to how I could make this possible? Could you please demonstrate how the index/match function would work if all data was 'easier'?

Sorry for being a pain, just been trying to work this out for a while now. Essentially I am trying to device a general journal format template for easy upload into a finance system.

Thanks again.