Это отличное применение для динамической установки 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
состоит в том, чтобы повторять поиск из «следующей строки вниз»),