EXCEL vlookup для копирования только 2-го экземпляра с листа 2 на лист 1

Question

У меня есть список кодов клиентов и электронных писем на Листе 1 (AC EMAIL), с кодом клиента, перечисленным в столбце A, и электронным письмом в столбце B.

Я использовал vlookup, чтобы скопировать клиентскую электронную почту на Sheet2 (AC ADDRESS), содержащий код клиента и его почтовый адрес.

Я использовал:=VLOOKUP(A3,'AC EMAIL'!$A$1:$B$25989,2,FALSE)

Однако некоторые клиенты появляются на Листе 2 или 3 раза, поскольку у них есть несколько адресов электронной почты.

Я хочу извлечь только 2-е и 3-е письмо на Sheet2 в другой почтовый столбец.

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

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

Это оказалось менее простым, чем я надеялся. Ценю любую помощь.

Answer 1

Учитывая, что "VLOOKUP() не поддерживает извлечение второго или третьего (или любого другого, кроме первого) совпадения. Предполагается найти первое совпадение, которое соответствует критериям поиска. "-

Вы должны сделать поиск уникальным. Если вы создаете столбец для последовательности, который увеличивается для удвоений и начинается заново для нового - например, если (c2 = c3,+b2+1,1), а затем создаете ключевой столбец, который включает последовательность - +c3 & b3, то вы можете выполнить поиск по любой последовательности, которую вы хотите.

Answer 2

VLOOKUP() не поддерживает извлечение второго или третьего (или любого другого, кроме первого) совпадения. Предполагается найти первое совпадение, которое удовлетворяет критериям поиска.

Ваш вопрос не говорит точно, что вы всегда хотите второй, или всегда третий - или всегда последний матч.
Предполагая, что вы всегда хотите последнее совпадение, одним из способов будет соответствующая сортировка массива. Независимо от того, какие данные делают запись, которую вы хотите поместить в последнюю позицию, можно использовать ее для сортировки в первую позицию (путем сортировки этого столбца в другом направлении). Если текущая последовательность является просто исторической от ввода, вы можете добавить столбец «Активный» и поставить туда «Х» (а затем отсортировать по убыванию) и т. Д .; или добавить столбец с действительной датой, или что-то в этом роде.
Кроме того, вы можете идентифицировать и отбросить все дубликаты, оставив только записи «last» = «wanted»; в зависимости от того, хотите ли вы оставить других так или иначе.

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

Answer 3

Это отличное применение для динамической установки INDEX MATCH , использующей INDIRECT для перенаправления искомых диапазонов.

Начнем с тривиального случая:

=INDEX(A2:B10,MATCH(D2,A2:A10,0),2)

Получив список кодов и имен, перейдите к (первому) коду соответствия строк и верните имя из этой строки.

Но как получить вторую запись?

Перебирайте список, ищите только остальную часть после первого появления. С помощью MATCH мы знаем, где появляется первая совпадающая запись - поэтому мы будем использовать INDIRECT чтобы повторить ту же функцию INDEX MATCH , но ограничив диапазон частью списка после того, как найдено первое совпадение.

Другими словами, используйте ту же формулу, но замените A2:B10 на INDIRECT("A"&MATCH(D2,$A$2:$A$10,0)+2&":B10") (затем оберните все это с помощью IFERROR для внешнего вида )

Теперь у нас есть:

=IFERROR(INDEX(INDIRECT("A"&MATCH($D2,$A$2:$A$99,0)+2&":B99"),MATCH($D2,INDIRECT("A"&MATCH($D2,$A$2:$A$99,0)+2&":A99"),0),2),"not found")

Вы заметите, что этот метод основан на сортировке столбца Client Code - мы жестко закодировали +2 в ссылку на ячейку, которая определяет диапазон - помните, что исходный диапазон начинался с A2 , а не A1 , поэтому мы добавляем одну строку к нагнать, затем мы добавим еще одну строку, чтобы начать диапазон со следующей строки вниз.

Что должно ясно дать понять, что мы собираемся делать дальше:

Просто измените это +2 на +3 чтобы начать диапазон поиска со строки NEXT вниз (пропуская первые два совпадающих экземпляра), чтобы получить третий экземпляр для следующего столбца.

Но что, если у нас много столбцов или мы просто не любим обновление формул?

Наконец, мы также заменим эти смещения +2 и +3 ссылками на ячейки, используя COLUMN . Это делает формулу расширяемой по горизонтали без ручного редактирования при копировании:

=IFERROR(INDEX(INDIRECT("A"&MATCH($D2,$A$2:$A$99,0)+COLUMN()-4&":B99"),MATCH($D2,INDIRECT("A"&MATCH($D2,$A$2:$A$99,0)+COLUMN()-4&":A99"),0),2),"not found")

Обратите внимание, что, поскольку у меня есть примерные данные назначения, начинающиеся в ячейке E2 , мое смещение COLUMN необходимо вручную установить один раз на COLUMN()-4 но как только это произойдет, эту формулу можно записать в верхнюю левую ячейку вашего диапазон назначения и копируется вниз и поперек столько раз, сколько вам нужно (опять же, если исходные данные отсортированы по коду, так как вся предпосылка итеративного INDEX MATCH состоит в том, чтобы повторять поиск из «следующей строки вниз»),