Find substring within a string, from a list

Hey All,

I need to figure out how to search a string of text in a single cell, reference a list and display back the column next to it.

Example:

Items to Search

ABC	5555
DEF	6666
GHI	7777

Cells to Search

1234ABCtgde	Formula here should return 5555
ewhhfruDEFrefjoi	Formula here should return 6666
ewhhfrdwefurefjoi	Formula here should return Nothing
fretgtevdGHIerfrfe	Formula here should return 7777

The formula needs to be something I can just drag down the column and have populate.

I hope that is enough detail. Any help you could provide would be helpful.

Thanks AlKey,

That did work! I do have two other issues with it. The list (D & E columns) are changeable so their may be some blank cells in the list how would I tweak that formula to have the list ignore blanks?

Also, what is the significance of the 25^25?

And one more question. If column A had a column next to it that was a dollar value.

And I wanted my reference table to look and find the match then verify that it is in the range. and if not have it continue to find the next match that meets the dollar range

Like this:

Row\Col A B C D E F G H
1 1234ABCtgde 49.99 5555 Lookup Return Min $$ Max $$
2 ewhhfruDEFrefjoi 19.99 <Blank> ABC 5555 0 999.00
3 ewhhfrdwefurefjoi 12.14 <Blank> DEF 6666 30.00 100.00
4 fretgtevdGHIerfrfe 50.99 7777 GHI 7777 0 500.00
5 sefsfGHIaasfdd 1000.00 8888 GHI 8888 500.00 10,000.00


Capture.PNG

(Also How did you insert that table so nicely)