У меня есть две таблицы, которые выглядят так:
Я бы хотел, чтобы в Excel использовалось значение в ID company associated с таблицей 2 (зеленый), найдите его в таблице 1 (оранжевое) и верните значение из Company name или несколько значений, когда несколько компаний связаны с одним человеком в таблице. 2.
Какое будет лучшее решение?
Вот формула, которая будет работать в Excel 2016, как есть. В более ранних версиях Excel требуется UDF с полизаполнением для TEXTJOIN() . (См. Этот пост для основного.)
Массив введите (Ctrl+Shift+Enter) следующую формулу в F2 и скопируйте-вставьте / заполните вниз в оставшуюся часть столбца:
{=TEXTJOIN(";",TRUE,INDEX(B:B,N(IF(1,MATCH(--MID(SUBSTITUTE(E2,";",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1)))=1),99),A:A,0)))))}
Обратите внимание, что эта формула работает только в том случае, если значения в столбце A фактически хранятся в виде чисел. Для текстовых значений --MID(…) в формуле необходимо заменить на TRIM(MID(…)) .
Предварительно подтвержденная формула выглядит следующим образом:
{=
TEXTJOIN(
";",
TRUE,
INDEX(
(B:B),
N(IF(1,
MATCH(
--MID(
SUBSTITUTE(E2,";",REPT(" ",99)),
99*(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)
+(1=ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))),
99
),
(A:A),
0
)
))
)
)}
Заметки:
(A:A) в предварительно сертифицированном варианте должны заставить A:A оставаться на своей собственной линии. То же самое относится к (B:B) .
Для Excel 2016 (только для Windows) должна работать следующая более простая формула:
{=TEXTJOIN(";",TRUE,INDEX(B:B,N(IF(1,MATCH(--FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b"),A:A,0)))))}
Как и предыдущая формула, она работает только со значениями, хранящимися в виде чисел. Для текстовых значений просто удалите -- из формулы.