Как получить массив значений vlookup без дополнительного столбца в Microsoft Excel?

Question

У меня есть список и таблица поиска. Список будет в столбце A, а таблица поиска - в столбцах C и D. Каждая ячейка в столбце C содержит текст и является уникальной. Столбец D имеет соответствующее значение для каждого элемента столбца C. Столбец A содержит несортированный список с дубликатами текста из столбца C, но ничего за пределами столбца C. Простой пример показан ниже.

В конечном итоге я хочу получить массив, содержащий соответствующие значения, найденные в таблице для всех столбцов А. Поэтому в этом примере я хочу

{1,2,1,2,2,2,1,1,2}

Я знаю, что это может быть достигнуто с помощью

=vlookup(B9,$C$1:$D$2,2,FALSE)

в каждой ячейке столбца B и возьмите столбец как массив {$ B $ 1:$ B $ 9}. Тем не менее, мне нужно пропустить посредника и исключить наличие чего-либо в другом столбце, поскольку это необходимо выполнять одновременно для разных справочных таблиц, но не связанных, т. Е. Не для множественного поиска, мне просто нужно посмотреть на тот же список А под другим набор соответствующих значений D.

Я в полной растерянности, это кажется таким простым, но я пытался часами ходить по кругу. Я вспоминаю, что читал, что вы не можете вывести vlookup в массив, и мне тоже не повезло, пытаясь использовать match. Так что действительно любая помощь будет оценена! Спасибо!

Answer 1

Для этого нет способа без макросов.

Вы не можете избежать использования VLOOKUP и он не может управлять массивом как параметром "lookup value".

Я думаю, что лучший вариант - использовать вспомогательный столбец, как вы уже описали.

Answer 2

Это можно сделать с помощью одной формулы в одной ячейке, используя довольно новую функцию Excel TEXTJOIN . Это работает с Excel 2016, но не распознается в Excel 2010, что приводит к #NAME? Я не знаю об Excel 2013 и хотел бы, чтобы кто-нибудь протестировал его.

Существуют более эффективные способы достижения результата, но они не соответствуют вашим ограничениям, поэтому это не лучшее решение для задачи, а именно то, что вы хотели.

Формула:

="{"&SUBSTITUTE(SUBSTITUTE(TEXTJOIN(",",FALSE,A1:A9),"a",INDEX(D1:D2,MATCH("a",C1:C2,0))),"b",INDEX(D1:D2,MATCH("b",C1:C2,0)))&"}"

Формула выше имеет жестко заданные значения поиска. Это облегчает прогнозирование выходных данных, но также делает формулу более громоздкой для написания и обслуживания, а также делает ее более слабой, чем ссылочная формула.

Приведенная ниже формула ссылается на ячейку, поэтому предсказать результат не так просто, но она гораздо более гибкая.

="{"&SUBSTITUTE(SUBSTITUTE(TEXTJOIN(",",FALSE,A1:A9),C1,INDEX(D1:D2,MATCH(C1,C1:C2,0))),C2,INDEX(D1:D2,MATCH(C2,C1:C2,0)))&"}"

Формула была протестирована с использованием списка с более чем 1000 строк без каких-либо проблем, и я предполагаю, что она будет работать до предела количества строк в Excel. Длина формулы линейно масштабируется с помощью справочной таблицы и может вместить чуть более 1000 строк. И сложность остается постоянной. Каждая новая строка в таблице поиска требует дополнительной SUBSTITUTE ( в начале формулы и CELL, INDEX( RANGE (MATCH (CELL, RANGE, 0))) в конце. Ничто в середине не должно быть изменено.