excel - Match partial data from multiple cells and return value

Question

I am trying to match partial data from multiple cells and want value in return. Tried index match and search function but no luck. If anyone can help.

Example:

Sheet1:

Cell "A1":
BGRAP1 - Gig0/2/17 
BGRAP22 - Gig0/2/16

Cell "A2":
 BRAMT2 - Gig0/2/0
 PTKLP8 - Gig0/2/16 
 PTKLP1 - Gig0/2/16 
 PTKLP8 - Gig0/2/17


Cell "B1":
S2165

Cell "B2":
S2218


Sheet 2:

Cell "A1": BGRAP1
Cell "A2": PTKLP8
Cell "A3": BGRAP22
Cell "A4": BRAMT2
Cell "A5": PTKLP8
Cell "A6": PTKLP1


Cell "B1": Gig0/2/17
Cell "B2": Gig0/2/17
Cell "B3": Gig0/2/16
Cell "B4": Gig0/2/0
Cell "B5": Gig0/2/16
Cell "B6": Gig0/2/16

Output: 
Cell "C1": S2165
Cell "C2": S2218
Cell "C3": S2165
Cell "C4": S2218
Cell "C5": S2218
Cell "C6": S2218

question from:https://stackoverflow.com/questions/65868237/match-partial-data-from-multiple-cells-return-value

Answer 1

In "Output" Sheet2 C1, formula copied down :

=LOOKUP(1,-SEARCH(A1&" - "&B1,Sheet1!$A$1:$A$2),Sheet1!$B$1:$B$2)