Formula to search contents of a cell against multiple sheets to find & return match

SB MS

Hi -

I have 3 grid sheets: one with client data, one with vendor data, and the 3rd I'm using like a checkbook register. I upload transaction data from our bank into the checkbook register sheet, but the transaction detail cell gives a long string of data. I want a formula that will search the transaction detail cell and pull out either the vendor name or the client name listed with the cell. The returning result is placed into another column on the checkbook register sheet.

Currently I am using this formula:

=IF(CONTAINS("Client Name1", [Transaction Detail]@row), "Client Name1") + IF(CONTAINS("Immediate Funds", [Transaction Detail]@row), "Check") + IF(CONTAINS("Client Name2", [Transaction Detail]@row), "Client Name2") + IF(CONTAINS("Client Name3", [Transaction Detail]@row), "Client Name3") + IF(CONTAINS("Client Name4", [Transaction Detail]@row), "Vendor Name1") + IF(CONTAINS("Vendor Name2", [Transaction Detail]@row), "Vendor Name2") + IF(CONTAINS("Vendor Name3", [Transaction Detail]@row), "Vendor Name3") + IF(CONTAINS("Vendor Name4", [Transaction Detail]@row), "Vendor Name4")

....etc, etc, etc

This formula somewhat works. If it finds a match, it does pull out the client/vendor name if found within the transaction detail cell, however it is a huge formula and does not automatically update as new vendors and clients are added to the Client Data or Vendor Data sheets. Plus, it also is adding in a 0 before the client or vendor name.

Is there a better formula that I can use that will search the transaction detail cell against both the clients and vendors sheets and thus automatically update as new clients and vendors are added to those grid sheets?

James Keuning

Can you provide a sample of the transaction detail cell? Just completely fake the data, but provide some samples. I think you want to use a formula to parse that data, because yeah, manually editing your current formula every time you get a new client or vendor is not sustainable.

SB MS

The data looks somewhat like this:

In the purple columns is where I have the formulas to pull out data