VLOOKUP text inside a string

Question

I have a column DeviceName in Sheet1 and it contains data like RTRNY653, SWNY653, ... In Sheet2 I have a column InterfaceName and it contains interface names and at the end the device name is listed too FA_01_RTRNY653, FA_12_SWNY653, ...

The standard formula to get the QoSID linked to the DeviceName would be =VLOOKUP(DeviceName;Sheet2!InterfaceName;2;0), but because InterfaceName contains more text, I'm stuck ...

Question: How can I create a VLOOKUP function that uses the DeviceName from Sheet1, looks it up in the InterfaceName on Sheet2 and gets me column QoSID from Sheet2.

Example data

Sheet1
DeviceName
-----------
RTRNY653
SWNY653
RTRWS215
RTRCH888

Sheet2
InterfaceName     | QoSID
--------------------------
FA_01_RTRNY653    |   1
FA_12_SWNY653     |   2
S_00_RTRWS215     |   1
S_01_RTRWS215     |   3

Answer 1

You can make use of wildcards like this:

=VLOOKUP("*"&DeviceName&"*";Sheet2!InterfaceName:QoSID;2;0)

Also, the reference table has to be two columns :)