Match substring

I have a table that looks like this:

Transaction	Type
TRANSFERRED FROM VS 123-4567890-1
REDEMPTION FROM CORE ACCOUNT
TRANSFER OF ASSETS WR76543210
TRANSFER OF ASSETS WR81234761
YOU SOLD CONF:13004NKGOZ 0
TRANSFERRED FROM VS 456-1246019-1

and another table that looks like this:

Key	Value
TRANSFERRED FROM	External Flow
TRANSFER OF ASSETS	External Flow
YOU SOLD	Sale

I'm trying to create a formula that will find the key that is a substring of each transaction (if there is one). So with the formula applied to the type column of the source table it should look like this:

Transaction	Type
TRANSFERRED FROM VS 123-4567890-1	External Flow
REDEMPTION FROM CORE ACCOUNT
TRANSFER OF ASSETS WR76543210	External Flow
TRANSFER OF ASSETS WR81234761	External Flow
YOU SOLD CONF:13004NKGOZ 0	Sale
TRANSFERRED FROM VS 456-1246019-1	External Flow

Note that you can't just use fuzzy matching (i.e. MATCH with match_type 1) because if none of the the values are a substring of the transaction in question, the type should be blank.

Any ideas? Strongly prefer a formula rather than a macro, if possible. Thanks!

With your sample data in A1:A7 (and assuming you're using Excel 2007 or later)

Please Login or Register  to view this content.

and this list in G1:H4

Please Login or Register  to view this content.

This regular formula searches for matched text and returns the corresponding value

Please Login or Register  to view this content.

Copy that formula down through B7

These are the results

Please Login or Register  to view this content.

Is that something you can work with?

Thanks a lot! Works great.