Excel INDEX MATCH Проверка нескольких столбцов

Question

По сути, проблема, которую я пытаюсь решить, - это VLOOKUP, который проверяет значение в столбцах A:E и возвращает значение, содержащееся в столбце F, если оно найдено в любом из них.

Поскольку VLOOKUP не соответствует задаче, я изучил синтаксис INDEX-MATCH, но я пытаюсь понять, как выполнить это для массива значений, а не для одного столбца. Я построил примерный набор данных ниже, чтобы попытаться объяснить это:

A------B------C------D------E------F

1------2------3------4------5------Apple

12-----13--------------------------Banana

14---------------------------------Carrot

Если проверяемая ячейка содержит 1, 2, 3, 4 или 5, то результатом формулы будет Apple. Если это 12 или 13, он должен вернуть банан и, наконец, если он содержит 14, он должен вернуть морковь.

Вторая половина этого происходит из-за того факта, что ссылка на ячейку является не одним значением, а самой полной таблицей. Таким образом, этот поиск будет выполняться большое количество раз в соответствии с различными значениями.

Таким образом, чтобы продемонстрировать, есть другая таблица в другом месте (как показано ниже), которая имеет эти значения в. Я пытаюсь заставить систему определить, какую строку и, следовательно, какое из значений «Яблоко, банан, морковь» связать с каждым столбцом. Таблица будет выглядеть так, как показано ниже

ПРИВЕТ------------

1 ------(Apple)----

2 ------(Apple)----

12 -----(банан)-

так далее.-----------------

Значения в скобках - это то, где формула вычисляет эти значения.

Answer 1

У вас есть несколько разных случаев. Давайте рассмотрим один случай:

Где-то в столбцах от A до E есть одна-единственная ячейка, содержащая 13, возвращающая содержимое ячейки в столбце F в той же строке.

Мы будем использовать "вспомогательный" столбец. В G1 введите:

=COUNTIF(A1:E1,13)

и скопировать вниз. Это позволяет нам идентифицировать строку:

Теперь мы можем использовать MATCH()/INDEX():

Выберите ячейку и введите:

=INDEX(F:F,MATCH(1,G:G,0))

Если "правила" изменятся и в строке может быть более 13 строк или несколько строк, содержащих 13 строк, мы изменим вспомогательный столбец.

EDIT # 1:

Основываясь на вашем обновлении, первым шагом будет вытащить жестко запрограммированное 13 из формул в столбце "помощник" и поместить его в собственную ячейку (скажем, H1). Затем вы можете запустить разные случаи, просто изменив одну ячейку.

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

Answer 2

Для одной формулы в H1:

=INDEX($F$1:INDEX(F:F,MATCH("ZZZ",F:F)),AGGREGATE(15,6,ROW($A$1:INDEX(E:E,MATCH("ZZZ",F:F)))/($A$1:INDEX(E:E,MATCH("ZZZ",F:F))=H1),1))

Это формула массива, поэтому мы должны ограничить ссылки размером набора данных. Весь INDEX(E:E,MATCH("ZZZ",F:F)) делает это. Это возвращает последнюю строку в столбце F, которая имеет текст. Затем он устанавливает это в качестве последней строки для повторения.

Метод @ Gary'sStudent избегает формул массива и может быть необходимым методом. По мере увеличения набора данных и числа формул время вычислений увеличивается. Даже до, в какой-то момент, крушения Excel. Обычно это занимает несколько тысяч, но я хочу сделать предупреждение.

---

РЕДАКТИРОВАТЬ

Чтобы не использовать формулы Array и все же быть одной формулой:

=IFERROR(INDEX(F:F,MIN(IFERROR(MATCH($H1,A:A,0),1050000),IFERROR(MATCH($H1,B:B,‌​0),1050000),IFERROR(MATCH($H1,C:C,0),1050000),IFERROR(MATCH($H1,D:D,0),1050000),I‌​FERROR(MATCH($H1,E:E,0),1050000))),"")

Это основано на ответе ОП, просто объединив этот метод в одну формулу.

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

И поскольку это не массив, ссылки на полные столбцы не наносят ущерба расчетному времени.

Answer 3

Основываясь на моих собственных исследованиях и обсуждениях с @ Gary'sStudent, решение, которое я использовал, заключалось в создании формулы MATCH для каждого из возможных столбцов, в которых могло бы содержаться значение, а также в выражении "IFERROR" для пробела.

I1 =IFERROR(MATCH($H1,A$1:A$3,0),"")     
J1 =IFERROR(MATCH($H1,B$1:B$3,0),"")     
K1 =IFERROR(MATCH($H1,C$1:C$3,0),"")    
L1 =IFERROR(MATCH($H1,D$1:D$3,0),"")    
M1 =IFERROR(MATCH($H1,E$1:E$3,0),"")
etc.

Эти столбцы теперь можно скрыть, чтобы предотвратить путаницу / взаимодействие с пользователем.

Затем я создал индекс, который накапливает их в одно значение, которое должно соответствовать рассматриваемой строке. Опять же, есть проверка (первая сумма), чтобы ввести это как пустое значение, если значение не найдено в таблице.

N1 =IF(SUM(I1:M1)=0,"",INDEX($A$1:$F$3,SUM(I1:M1),6))

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

A1:E3 Cell contains a blank value                [Formatting None Set, Stop if True]
A1:E3 =COUNTIF($A$1:$E$3,A1)>1                   [Formatting Text:White, Background:Red]

H1:N1 =COUNTIF($A$1:$E$3,H1)>1       [Formatting Text:Red, Background:Red]

Это всего лишь подсказка пользователю удалить эти дубликаты данных.

Answer 4

Другой метод будет основан на вспомогательной таблице, которая представляет, как это "должно" быть структурировано в первую очередь. Это позволило бы избежать уравнений монстров, которые раздражают отладку и изменение после этого, и она способна чисто решать различное количество столбцов, в отличие от идеи иметь 5 столбцов поиска.

Если вышеупомянутое находится в Листе 1, добавьте Лист 2. На этом месте четыре колонны; Строка, столбец, идентификатор, имя

Формула в Row должна быть (в псевдо-коде "последний" означает "для строки выше в sheet2")

=IF(Column = 1, Last row + 1 , Last row)

Формула в Column:

=IF(OR(Last Column = 5; INDEX(StartTable, last row, last column + 1) = ""), 1, Last column+1)

Формула в ID и Name:

=INDEX(StartTable, Row, Column)    
=INDEX(NameColumn, Row, 1)

Затем вы заполняете это (в основном, до тех пор, пока row > количество строк в исходной таблице).

Наконец, вы используете новую таблицу с обычным vlookup или index/match.

PRO: Гораздо более простые формулы, проще в использовании и понимании.

Минусы: Нужна дополнительная таблица, должна поддерживать длину таблицы. С точки зрения производительности существует риск, поскольку для этого в значительной степени требуется один поток для всей "строки" значений.

Кроме того, если пара строк ошибок в порядке, код может быть несколько проще и, возможно, более производительным, тогда мы можем предположить, что число столбцов всегда равно 5, давая как строку, так и столбец.