Find and replace multiple values from a list

I could figure out how to do this in vba, but I don't want to because of how you formatted the lookup.
Lookup
Product 2 = DELETED Product 2
Product 4 = DELETED Product 4
Does it really look like that? Like does A1 actually say "Product 2 = DELETED Product 2"? It says all of that? Why not just "DELETED Product 2"? If you did it that way, then I could run the REPLACE function to remove DELETED from the string value. Then I could run the TRIM function to get rid of spaces on the ends. Then I could do a wildcard search of "*Product 2*" for every non empty row. If your lookup table really looks like how you described with an equal sign and everything as part of the string, you made it too complicated and I don't want to work around that. I don't think anyone else wants to either or you would have had a reply by now. Can you change the lookup table to something easier? You don't even need to include the word deleted in the value. If the purpose of the lookup table is to log deleted items, then obviously everything in that table is deleted, so why state it in the cell value? All that does is create complication when writing vb code. Simple your dataset up and I could accomplish this in under 30 microts.

Thanks for your reply. I couldn't attach a file so I tried copying the information. Sorry it was unclear.
The data sits in different columns. I've added a comma below for each new column.

Table
Product 1, $100,000
Product 2, $5,000
Product 3, $120,000
Product 4, $2,000

Lookup
Product 2, DELETED Product 2
Product 4, DELETED Product 4

Outcome
Product 1, $100,000
DELETED Product 2, $5,000
Product 3, $120,000
DELETED Product 4, $2,000

In future posts, click on Go Advanced next to the Post Quick Reply button and insert a table.

Thanks for the tip. Please let me know if you'd like the information re-posted in tables to make it easier.

Alright lets take a look at the Outcome sheet. In cell A1 I want you to enter this formula...


In cell B1 enter this formula...


Copy the code down the column. Done. I know you said you wanted VBA, but this was way to easy as a formula.

Thanks for your quick solution WarPiglet.
I'd like to use a macro to apply the change because there are several data sheets (table). Are you able to help with the VBA code please?

Yes I can do it. I'm going to assume you want a subroutine rather than a function? Is this going to be an on change event or at the push of a button?

I'd like a push of the button to run it please
A standard subroutine would be great.

Taking a bit longer than I hoped. Have to start over. I accidentally created an infinite loop that just crashed my system.