匹配来自多个单元格的部分数据并返回值 [英] Match partial data from multiple cells and return value

问题描述

我正在尝试匹配来自多个单元格的部分数据，并希望获得回报.尝试过索引匹配和搜索功能，但没有运气.如果有人可以帮忙.

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.

示例:

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

推荐答案

在输出"中 Sheet2 C1 ，向下复制公式:

In "Output" Sheet2 C1, formula copied down :

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

这篇关于匹配来自多个单元格的部分数据并返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！