Поиск поля на основе двух или более столбцов

Опция 1:

Чтобы решить эту проблему, вам нужна одна ячейка HELPER, чтобы выбрать, какой код вы хотите использовать.

Запишите эту формулу в клетку F10.

=IFERROR(INDEX($A$3:$A$6,IF($C$9=1,MATCH($D$10,$D$3:$D$6,0),IF($C$9=2,MATCH($D$10,$G$3:$G$6,0)))),"Wrong Code") 

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

• В ячейке помощника C9напишите ваш выбор должен быть 1 или 2.
• Пишите Sub Codeв клетку D10.
• Формула будет искать Sub Codeв Column Dслучае C9 has 1, в противном случае проверить Sub Codeпо прибытиюColumn G.
• Если вы установили неправильную комбинацию в ячейках C9&, D10то формула вернет ошибку неправильного кода .

Отредактировано:

Вариант 2:

Эта формула массива также может быть использована в ячейке F10.

{=IFERROR(INDEX($A$3:$A$6,MATCH(1,($D$3:$D$6=$D$10)+($G$3:$G$6=$D$10),0)),"Wrong Code")} 

Заметка,

• Завершите приведенную выше формулу с помощью Ctrl+Shift+Enter.
• При работе с OPTION 2 нет необходимости записывать значение в вспомогательную ячейку C9, только Sub Codeв ячейку D10.

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

VLOOKUP требует поиска в крайнем левом столбце, чтобы он не работал. INDEX + MATCH будет работать, но MATCH не может искать несколько или двухмерные диапазоны. Обходной путь - объединить несколько МАТЧЕЙ для разных диапазонов.

Результат поиска в E7 использует эту формулу:

=IFERROR(INDEX(A2:A5,IFERROR(MATCH(E8,D2:D5,0),0)+IFERROR(MATCH(E8,G2:G5,0),0)),"Invalid Sub_code") 

Каждый столбец имеет свой МАТЧ. Совпадение будет происходить только в одном столбце, поэтому IFERROR возвращает ноль, если столбец не имеет соответствия. Затем добавление значений совпадения объединит индекс местоположения для столбца с совпадением и ноль для любых дополнительных искомых столбцов. Название вопроса гласит «две или более колонки». Это можно расширить, просто добавив другое выражение MATCH для каждого дополнительного столбца.

INDEX оборачивается своим собственным IFERROR на случай, если введен неверный Sub_code. MATCH не учитывает регистр, поэтому он все равно найдет совпадение, если только заглавные буквы не совпадают.

Конечно, вы можете иметь несколько ячеек ввода и соответствующих ячеек результатов.

альтернатива

@Rajesh S заметил, что это также может быть реструктурировано:

=IFERROR(INDEX(A2:A5,MATCH(E8,D2:D5,0)),IFERROR(INDEX(A2:A5,MATCH(E8,G2:G5,0)),"Invalid Sub_code")) 

Это использует каждое выражение MATCH с INDEX. IFERROR на INDEX также заботится о любых ошибках MATCH. IFERRORS затем вкладываются, поэтому ошибка в первом поиске переходит ко второму, а ошибка там - в неверное предупреждение Sub_code. Это заканчивается той же длины, потому что, в то время как счетчик функции IFERROR уменьшается, счетчик функции INDEX увеличивается. Это может быть увеличено для дополнительных столбцов путем дополнительного вложения.

Используйте ту версию, которая кажется наиболее интуитивной.

Для сравнения структуры и логики я разобью формулы.

Альтернатива 1:

=IFERROR( INDEX(A2:A5, IFERROR( MATCH(E8,D2:D5,0) ,0) + IFERROR( MATCH(E8,G2:G5,0) ,0) ) ,"Invalid Sub_code") 

Альтернатива 2:

=IFERROR(  INDEX(A2:A5, MATCH(E8,D2:D5,0) ) ,IFERROR( INDEX(A2:A5, MATCH(E8,G2:G5,0) ) ,"Invalid Sub_code") ) 

=IFERROR(INDIRECT("A"&IF(SUMPRODUCT(--($D$2:$D$5=$E$8),ROW($A$2:$A$5))=0, SUMPRODUCT(--($G$2:$G$5=$E$8),ROW($A$2:$A$5)), SUMPRODUCT(--($D$2:$D$5=$E$8),ROW($A$2:$A$5)))), IF(E8="","","Invalid code"))

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

Однако вернемся к моей формуле.

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

SUMPRODUCT(--($D$2:$D$5=$E$8),ROW($A$2:$A$5): Проверяет, содержит ли столбец D совпадение. Если это так, он возвращает номер строки совпадения. Если совпадений не найдено, возвращается 0.

SUMPRODUCT(--($G$2:$G$5=$E$8),ROW($A$2:$A$5): Проверяет, содержит ли столбец G совпадение. Если это так, он возвращает номер строки совпадения. Если совпадений не найдено, возвращается 0.

IF(...=0, ..., ...): Проверяет, вернул ли поиск по столбцу D 0, т.е. не найдено ни одного совпадения. В этом случае он возвращает результат для столбца G. В противном случае он просто возвращает результат столбца D.

INDIRECT("A"&...): Номер строки из SUMPRODUCTs (или 0, если нет совпадений) объединяется с «A» - буквой первого столбца. Затем INDIRECT функция находит ячейку с этой ссылкой. Например, он может найти ячейку с именем «A3» или, если совпадение не найдено, «A0» - несуществующую ячейку.

IFERR(..., ...): Проверяет и обрабатывает ошибки. Если столбец D не содержит совпадений, он вернет поиск по столбцу G. Однако, если столбец G тоже ничего не содержит, то «A0» будет просто передан INDIRECT функции, которая не является реальной ячейкой. Следовательно, это приведет к ошибке, и вместо ее отображения будет запущен обработчик ошибок.

IF(E8="","","Invalid code")): Обрабатывает ошибку. Если ячейка ввода пуста, она также остается пустой, так как не нужно суетиться. Однако, если оно не пустое, но все равно есть ошибка, возвращается «Неверный код» , поскольку это может означать только то, что что-то было введено, и что это что-то было недопустимым.

Примеры

Функционально:

Пустой ввод:

Неправильный ввод: