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

511
Sokunthaneth

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

Например, я хочу Johnпечатать в E7, когда я печатаю, Ag1или Ps1в E8.

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

Spreadsheet table

1
@ fixer1234, в основном, я хочу, чтобы `John` печатался в желтом поле курсора, когда я набираю` Ag1` или `Ps1` в этом поле. Я не уверен, какую формулу использовать. Sokunthaneth 5 лет назад 0
Просто чтобы уточнить, является ли требование, чтобы вход и результат были в одном поле? Если это так, вам понадобится VBA, потому что типизированный ввод заменит любую функцию в ячейке. fixer1234 5 лет назад 0
@ fixer1234 извините, это другое окно. Не нужно использовать одну и ту же коробку. скажем, вывести его в сером поле, когда я введу `sub_code` в желтом поле курсора. Sokunthaneth 5 лет назад 0
Есть ли возможность для более чем одного профессора иметь один и тот же Sub_code? fixer1234 5 лет назад 0
@ fixer1234 один профессор может иметь один или несколько `sub_code`, но не может иметь тот же` sub_code`. Каждый `sub_code` уникален. Sokunthaneth 5 лет назад 0
На самом деле, мой вопрос был больше, у Джона и Филиппа был один и тот же субкод. fixer1234 5 лет назад 0
@ fixer1234 Нет, они не могут иметь одинаковый `sub_code`. Sokunthaneth 5 лет назад 0

3 ответа на вопрос

1
Rajesh S

Опция 1:

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

enter image description here

Запишите эту формулу в клетку 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.

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

Я думаю, что проблема OP в том, что Sub_code может быть в любом столбце, а вы не знаете, какой именно. Таким образом, задача состоит в том, чтобы просто искать его в нескольких столбцах (а не вручную пробовать каждый столбец, пока не найдете соответствие). fixer1234 5 лет назад 0
@ fixer1234, теперь я отредактировал сообщение и включил формулу массива для автоматического поиска подкода в обоих столбцах! ☺ Rajesh S 5 лет назад 0
Вариант 2: намного лучше! :-) В примерах нет ссылок на строки или столбцы, поэтому немного сложно следовать. Следует также упомянуть, что он чувствителен к регистру, и добавить пару предложений, объясняющих, как это работает. +1 (и просто удалите опцию 1, так что это идет прямо к решению.) fixer1234 5 лет назад 0
@ fixer1234 ,, Формула не возражает против случая, так как рассматривает как Верхний, так и Нижний ,, 1 для Туре, который всегда был в Col D или G. Rajesh S 5 лет назад 0
В LO Calc это не совпадало, если не совпадал регистр. Думаю, мы нашли другой пример, в котором Excel и Calc отличаются. В моем решении, матч работал в любом случае. Ваш использует равенство, и Calc требует соответствующего случая для этого. fixer1234 5 лет назад 0
0
fixer1234

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

enter image description here

Результат поиска в 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") ) 
0
Geza Kerecsenyi

=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")): Обрабатывает ошибку. Если ячейка ввода пуста, она также остается пустой, так как не нужно суетиться. Однако, если оно не пустое, но все равно есть ошибка, возвращается «Неверный код» , поскольку это может означать только то, что что-то было введено, и что это что-то было недопустимым.

Примеры

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

'He1' is written in E8, E7 returns 'Phillip'.

'Kh1' is written in E8, E7 returns 'Deav'.

Пустой ввод:

Both E7 and E8 are empty

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

'Rn1' is written in E8, E7 returns 'Invalid code'

Это работает и демонстрирует другой подход, но я думаю, что он просто более замысловатый, а не более элегантный или стабильный. Это также не является чисто масштабируемым, если OP должен добавить дополнительные наборы столбцов (заголовок говорит, что «два или более столбцов», так что это ожидается). Формула в два раза длиннее, и я упускаю преимущество. Но это правильный подход, и ответ очень хорошо написан; Вы хорошо объяснили, как это работает. fixer1234 5 лет назад 0

Похожие вопросы