Trouble searching multiple columns for matching text and bringing back specific data

[Wellsie78]

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).<o></o>
I currently have 3 columns of data that I am trying tosearch to provide particular values for each instance of matching data.<o></o>
Please find below an extract of data;<o></o>
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD]

<o></o>​

[/TD]
[TD="width: 130, bgcolor: transparent"]

A<o></o>​

[/TD]
[TD="width: 124, bgcolor: transparent"]

B<o></o>​

[/TD]
[TD="width: 133, bgcolor: transparent"]

C<o></o>​

[/TD]
[TD="width: 126, bgcolor: transparent"]

D<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]SUPPLIER NAME<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]SUPPLIER CODE<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]NARRATION<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]REQUIRED OUTCOME (by formula)<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]1<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]AVIS AUSTRALIA<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]04321<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]Purchase BestBuys.com<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]2<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]DE NEEFE SIGNS<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]04407<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]Purchase Whitcoulls Gallery<o></o>
<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]3<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]RICOH AUSTRALIA<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]04441<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]Purchase (NZD 17.36) Whitcoulls Internet Store<o></o>
<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]4<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]ZALLCOM<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]04444<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]Purchase Legato Espresso Cafe<o></o>
<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]5<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]LEASE PLAN<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]04567<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]Purchase The Stamford Plaza Sydney<o></o>
<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]04660<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]6<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]STAMFORD PLAZA<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]04660<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]Purchase The Stamford Plaza Sydney<o></o>
<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]04660<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]7<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]DARWIN CITY COUNCIL<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]04737<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]Purchase The Stamford Plaza Sydney<o></o>
<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]04660<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]8<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]TOSHIBA AUSTRALIA PTY LTD<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]04802<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]Purchase Profile Publishing<o></o>
<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]9<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]COMPLETE OFFICE SUPPLIES<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]04823<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]Purchase Gecko Printing<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]10<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]THOMSON REUTERS<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]04973<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]Purchase Stamford Sydney Airport Hotel<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]05436<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]11<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]STAMFORD SYDNEY AIRPORT HOTEL<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]05436<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]Purchase Kwik<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]12<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]TEMPORARY FENCING PTY LTD<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]05494<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]Purchase (15.65USD) Ricoh Australia<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]13<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]DEPT FOREIGN AFFAIRS & TRADE<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]05621<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]14<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]KWIK COPY PRINTING<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]05675<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]15<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]ADELAIDE CITY COUNCIL<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]05830<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]16<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]FOUR SEASONS HOTEL SYDNEY<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]05934<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]17<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]SOUND ADVICE<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]06233<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: transparent"]18<o></o>
[/TD]
[TD="width: 130, bgcolor: transparent"]CREDIT CARD – ONE OFF CREDITORS<o></o>
[/TD]
[TD="width: 124, bgcolor: transparent"]99999<o></o>
[/TD]
[TD="width: 133, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 126, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
</tbody>[/TABLE]
<o></o>
So what I am trying to achieve is;<o></o>
I want for each instance of a particular supplier referencedin column C, for a search to be done on column A, and for the correspondingsupplier code in column B to be brought back to column D.<o></o>
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.<o></o>
For narrations that do not have a corresponding suppliername 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 D299999.<o></o>
Columns A and B are a listing out of the finance system andtherefore has potentially thousands of values, column C is out of a credit cardsystem and potentially has hundreds of values.<o></o>
I have used various formulas incorporating wildcards in IFstatements, COUNTIF, VLOOKUPS….<o></o>
I would be really appreciative of any advice or outcomesthat can be offered.<o></o>
Thank you in advance J<o></o>