Text replacement formula - VLOOKUP?

[roskalov]

Hi everyone,


I have attached a spreadsheet with what I am struggling with.

I have a database with thousands of suppliers, either contracted or uncontracted. I have filtered all transactions with uncontracted suppliers (there may be many transactions followed one by one) - this is the left hand side of the spreadsheet.

To the right I have identified the companies that I want to see as contracted. Thus, I need to change all cells in B column related to one company in accordance with its real status (taken from the right hand side).

i.e. B2:B5 should change to "Contracted", as well as B12:B18 and so on.

In this case I have done three companies to change (Wolf, Fox and Hare) but in reality there are hundreds.

Hope you could help.

File attached.


Kind regards,

Vladislav

[roskalov]

Company Status Spend
Fox Uncontracted £532
Fox Uncontracted £525
Fox Uncontracted £121
Fox Uncontracted £87
Bear Uncontracted £196
Bear Uncontracted £97
Bear Uncontracted £597
Bear Uncontracted £28
Bear Uncontracted £425
Bear Uncontracted £449
Wolf Uncontracted £358
Wolf Uncontracted £692
Wolf Uncontracted £345
Wolf Uncontracted £558
Wolf Uncontracted £83
Wolf Uncontracted £176
Wolf Uncontracted £672
Hare Uncontracted £332
Hare Uncontracted £931
Hare Uncontracted £139
Hare Uncontracted £480
Hare Uncontracted £230
Hare Uncontracted £51
Lizard Uncontracted £339
Lizard Uncontracted £175
Lizard Uncontracted £574
Lizard Uncontracted £829
Lizard Uncontracted £860


________________________________

Fox Contracted
Hare Contracted
Wolf Contracted

[roskalov]

Sorry, no attachments allowed for some reason. Company is column A, status - B and spend - C

I hope it does make sense but if not I am happy to elaborate further.

[AliGW]

Yes, attachments are allowed!

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[roskalov]

Testing attachment

[roskalov]

Thank you, AliGW, you are a star!

[oeldere]

And how does the result looks like?

Please add this in the same file and post the file again.

[roskalov]

Please see attached.

So the info from little table should replicate to the big one. The little table's company names do not duplicate (unlike the main table) but wherever the company name from big table is identical to the little one, all the transactions with it must be switched from uncontracted to contracted.

Hope that makes sense

[oeldere]

d2=IFERROR(VLOOKUP(A2,$F$1:$G$3,2,0),B2) and drag down.

See the attached file.

[roskalov]

oeldere, thank you very much indeed, case solved!

[oeldere]

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.