Поиск значения в Excel между вкладками

Question

У меня есть функция, которая будет принимать значение из столбца B на Sheet1 и искать совпадение в столбце D на Sheet2 и возвращать это значение, если оно найдено.

= INDEX(Лист2!$ D: D $, MATCH(B2, Лист2!$ D: D $, 0))

Тем не менее, я на самом деле функция для поиска соответствия в нескольких столбцах Sheet2 (DZ), а не только столбец D.

Заранее спасибо за вашу помощь!

Answer 1

Это немного излишне. Конкатенация индекса соответствия должна работать.

=IFERROR(INDEX(Sheet2!$D:$D,MATCH($B2,Sheet2!$D:$D,0)),IFERROR(INDEX(Sheet2!$E:$E,MATCH($B2,Sheet2!$E:$E,0)),IFERROR(INDEX(Sheet2!$F:$F,MATCH($B2,Sheet2!$F:$F,0)),IFERROR(INDEX(Sheet2!$G:$G,MATCH($B2,Sheet2!$G:$G,0)),IFERROR(INDEX(Sheet2!$H:$H,MATCH($B2,Sheet2!$H:$H,0)),IFERROR(INDEX(Sheet2!$I:$I,MATCH($B2,Sheet2!$I:$I,0)),IFERROR(INDEX(Sheet2!$J:$J,MATCH($B2,Sheet2!$J:$J,0)),IFERROR(INDEX(Sheet2!$K:$K,MATCH($B2,Sheet2!$K:$K,0)),IFERROR(INDEX(Sheet2!$L:$L,MATCH($B2,Sheet2!$L:$L,0)),IFERROR(INDEX(Sheet2!$M:$M,MATCH($B2,Sheet2!$M:$M,0)),IFERROR(INDEX(Sheet2!$N:$N,MATCH($B2,Sheet2!$N:$N,0)),IFERROR(INDEX(Sheet2!$O:$O,MATCH($B2,Sheet2!$O:$O,0)),IFERROR(INDEX(Sheet2!$P:$P,MATCH($B2,Sheet2!$P:$P,0)),IFERROR(INDEX(Sheet2!$Q:$Q,MATCH($B2,Sheet2!$Q:$Q,0)),IFERROR(INDEX(Sheet2!$R:$R,MATCH($B2,Sheet2!$R:$R,0)),IFERROR(INDEX(Sheet2!$S:$S,MATCH($B2,Sheet2!$S:$S,0)),IFERROR(INDEX(Sheet2!$T:$T,MATCH($B2,Sheet2!$T:$T,0)),IFERROR(INDEX(Sheet2!$U:$U,MATCH($B2,Sheet2!$U:$U,0)),IFERROR(INDEX(Sheet2!$V:$V,MATCH($B2,Sheet2!$V:$V,0)),IFERROR(INDEX(Sheet2!$W:$W,MATCH($B2,Sheet2!$W:$W,0)),IFERROR(INDEX(Sheet2!$X:$X,MATCH($B2,Sheet2!$X:$X,0)),IFERROR(INDEX(Sheet2!$Y:$Y,MATCH($B2,Sheet2!$Y:$Y,0)),IFERROR(INDEX(Sheet2!$Z:$Z,MATCH($B2,Sheet2!$Z:$Z,0)))))))))))))))))))))))))

Идея: если поиск по столбцу D не удался (выдает ошибку), то посмотрите в E: E. Если совпадение столбца E не удалось, посмотрите в столбце F ... до Z.

Надеюсь, поможет. (:

Answer 2

Вы можете использовать формулу Array (CSE), объединяющую INDEX, MATCH, MMULT & TRANSPOSE, чтобы найти значение Lookup путем сопоставления по нескольким столбцам.

Формула в E27 в листе 1:

{=INDEX(Sheet2!$D$21:$D$24,MATCH(1,MMULT(--(Sheet2!$E$21:$G$24=D27),TRANSPOSE(COLUMN(Sheet2!$E$21:$G$24)^0)),0))}

NB. Завершите формулу с помощью Ctrl+Shift+Enter и заполните ее.

Как это устроено:

--(Sheet2!$E$21:$G$24=D27)

• Создает ИСТИНА / ЛОЖЬ и -- знак преобразует их в 1 и 0 .
• MMULT используется для выполнения умножения матриц, и результат передается в функцию MATCH в виде массива с 1 в качестве значения Lookup.

TRANSPOSE(COLUMN(Sheet2!$E$21:$G$24)^0)

• Где COLUMN используется для создания числового массива с 3 Col by 1 Row, а TRANSPOSE преобразует этот массив в 1 Col & 3 Rows.
• Power of Zero преобразует все числа в массиве в 1.
• MATCH возвращает позицию первого матча, которая соответствует строке первого матча. Строка соответствует критериям.
• Index возвращает Area , к которой принадлежат Alex и так далее.

При необходимости измените ссылки на ячейки в формуле.