Excel 2013 Поиск подстроки в столбце с использованием столбца поиска и возврат сопутствующего значения

Question

У меня 2 листа:

лист1 содержит 2 столбца следующим образом:

columnA   columnB
AB        Texas
BC        California
DE        Virginia

лист2 содержит 1 столбец следующим образом:

columnA
AB_documentOne.pdf   
BC_documentTwo.pdf   
DE_documentThree.pdf 
AB_documentFour.pdf  
AB_documentFive.pdf  
DE_documentSix.pdf  

В соседнем столбце "columnB" в sheet2 я хотел бы извлечь правильные значения из columnB в sheet1.

Желаемый результат:

columnA               columnB
AB_documentOne.pdf    Texas
BC_documentTwo.pdf    California
DE_documentThree.pdf  Virginia
AB_documentFour.pdf   Texas
AB_documentFive.pdf   Texas
DE_documentSix.pdf    Virginia

Я старался:

=INDEX(sheet1!B:B,MATCH(sheet1!A:A,sheet2!A:A,0))

Поскольку я использую columnA из sheet1 в качестве поиска подстроки для columnA в sheet2, это приведет к ошибке.

Версия Excel: 2013

Спасибо за вашу помощь.

Answer 1

Я смог заставить следующее работать с модом LEFT(string, 2) для вашего поиска, переключить порядок параметров соответствия и переключить A:A в определенную ячейку, но привязать столбец, чтобы вы могли скопировать его вниз колонка без вопроса:

=INDEX(Sheet1!B:B,MATCH(LEFT(Sheet2!$A1,2),Sheet1!A:A,0))

РЕДАКТИРОВАТЬ ДЛЯ СЛЕДУЮЩЕГО КОММЕНТАРИИ:

Чтобы найти целевую строку где-нибудь в полном тексте, единственный способ, которым я смог заставить ее работать, - это использовать формулы массива (нужно ввести их, нажав CTRL+SHIFT+ENTER) и использовать трюк COUNTIF (), который я нашел здесь для первого найти индекс, где целевая строка начинает совпадать с полным текстом.

COUNTIF() находит, какая из ваших целевых строк найдена (на самом деле он дает вам количество совпадений), затем использует индекс для какой строки имеет 1 совпадение с помощью MATCH (), а затем ваш INDEX () получает соответствующее имя состояния это соответствует совпадению, которое было найдено.

=INDEX(Sheet1!B:B,MATCH(1,COUNTIF(A1,"*"&Sheet1!A:A&"*"),0))