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

Question

Я хочу вывести поле professor на основе ввода в желтых полях. На входе будет возможный sub_code column D ИЛИ column G Я пробовал VLOOKUP(...) и INDEX(...,MATCH(...)) , но я застреваю при попытке выполнить: either column D or column G

Например, я хочу, чтобы John печатался на E7, когда я Ag1 или Ps1 на E8.

Как я мог написать функцию для этого случая?

Answer 1

Опция 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 .

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

Answer 2

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")  )

Answer 3

=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"&...) : номер строки из SUMPRODUCT s (или 0, если нет совпадения) объединяется с "A" - буквой первого столбца.Функция INDIRECT затем находит ячейку с этой ссылкой.Например, он может найти ячейку с именем "A3" или, если совпадение не найдено, "A0" - несуществующую ячейку.

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

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

Примеры

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

Пустой ввод:

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