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)

Transaction
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 this list in G1:H4

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

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

B2: =IFERROR(LOOKUP(10^10,SEARCH($G$2:$G$4,A2),$H$2:$H$4),"")

Copy that formula down through B7

These are the results

External Flow
(blank)
External Flow
External Flow
Sale
External Flow

Is that something you can work with?

Thanks a lot! Works great.