EXCEL - Lookup value in a secondary list and return match

Google Sheets
=============
QUERY(Sheet1!A$2:C, "Select A Where C contains '"&$F2&"'",-1)
QUERY(Sheet1!A$2:C, "Select A Where C matches '"&$F2&"'",-1)
QUERY(Sheet1!A$2:C, "Select A Where C='"&$F2&"'",-1)
The best Vlookup alternative. This uses Google Query tech.
It will return all matches in seperate cells.

QUERY(Sheet1!A$2:C, "Select A Where C matches '"&$F2&"' limit 2",-1)
This will limit the output to 2 matches.

=join(", ",(QUERY(Sheet1!A$2:C, "Select A Where C matches '"&$F2&"' limit 1",-1)))
This will put all output in the same cell, comma separated.




EXCEL
=====
=IF(ISNUMBER(SEARCH("abc",B5)),"return if B5 partially or fully contains text abc","no match")

Return multiple values:
=IF(ISNUMBER(SEARCH("vpaid",E3)),"VPAID",IF(ISNUMBER(SEARCH("VAST4",E3)), "VAST4", "-"))

Recommended (it will return the last found match):
=IFERROR(LOOKUP(2,1/SEARCH(Publishers,B2),Publishers),"Other")
Publishers is a named range, you can also use for example E2:E50.
Others is the value shown when no match was found in the Publishers list.

The same but with an Array Formula (I saw the same result in every cell so it did not work for me):
=INDEX(Publishers,MATCH(TRUE,ISNUMBER(SEARCH(Publishers,K2)),0))

A very manual solution:
=IF(K2="300x250","Desktop standard",IF(K2="336x280","Desktop standard",IF(K2="728x90","Desktop standard",IF(K2="468x60","Desktop standard",IF(K2="120x600","Desktop standard",IF(K2="160x600","Desktop standard",IF(K2="300x600","Halfpage",IF(K2="970x250","Billboard",IF(K2="120x90","Floorad",IF(K2="970x500","Fishtank",IF(K2="2x4","Skin", IF(K2="8x8","HPTO",IF(K2="1x1","HPTO","Other")))))))))))))


=IF(K2="SmartClip","Desktop standard",IF(K2="336x280","Desktop standard",IF(K2="728x90","Desktop standard",IF(K2="468x60","Desktop standard",IF(K2="120x600","Desktop standard",IF(K2="160x600","Desktop standard",IF(K2="300x600","Halfpage",IF(K2="970x250","Billboard",IF(K2="120x90","Floorad",IF(K2="970x500","Fishtank",IF(K2="2x4","Skin", IF(K2="8x8","HPTO",IF(K2="1x1","HPTO","Other")))))))))))))