#excel #excel-formula #excel-2016
#excel #excel-формула #excel-2016
Вопрос:
Есть ли способ, которым я могу выполнить ВПР с 3 разными условиями поиска в одной таблице? Например, я хочу выполнить поиск по SamAcctName или UserLogonName или TestName в одном большом пуле данных
Search terms
SamAcctName | UserLogonName | TestName | result |
---------------|------------------------|-------------|------------------|
test.user | testuser@test.com | testus | Result 1 <<<<<< |
---------------|------------------------|-------------|------------------|
estuser | other@test.com | testuseer | Result 2 <<<<<< |
---------------|------------------------|-------------|------------------|
Table with all data records
Name | Description | moredata |
--------------|-------------------------|--------------------|
Test.User | Result 1 <<<<<<< | Blah Blah BLah |
--------------|-------------------------|--------------------|
other@test.com| user ..Blah Blah | Blah Blah BLah |
--------------|-------------------------|--------------------|
other.user | user ..Blah Blah | Blah Blah BLah |
--------------|-------------------------|--------------------|
TestUser2 | user ..Blah Blah | Blah Blah BLah |
--------------|-------------------------|--------------------|
other.user | user ..Blah Blah | Blah Blah BLah |
--------------|-------------------------|--------------------|
other@test.com| Result 2 <<<<<<< | Blah Blah BLah |
--------------|-------------------------|--------------------|
other.user | user ..Blah Blah | Blah Blah BLah |
--------------|-------------------------|--------------------|
Ответ №1:
Простым подходом с использованием кувалды было бы написать вложенную IFERROR(VLOOKUP... формулу, подобную приведенной ниже.
=IFERROR(VLOOKUP(A2,F:G,2,0),IFERROR(VLOOKUP(B2,F:G,2,0),IFERROR(VLOOKUP(C2,F:G,2,0),"")))
где столбцы F amp; G содержат всю таблицу данных, а первая таблица находится в A2:D3 диапазоне.
Ответ №2:
=INDEX(D1:D3,MATCH(1,MMULT(--(A1:C3=E1),TRANSPOSE(COLUMN(A1:C3)^0)),0))
Это функция массива, которую нужно ввести с помощью ctrl shift enter
Массив создается A1:C3=E1 как 1 или 2 (TRUE или FALSE). Массив состоит из 3 столбцов и длины вашего диапазона. Создается второй массив с TRANSPOSE(COLUMN(A1:C3)^0) результатом {1, 2, 3} , равным 0 {1, 1, 1} . MMULT умножает эти массивы, и результатом является номер строки вашего соответствующего значения. ИНДЕКС показывает значение результата в столбце индекса этой строки.
Редактировать: я только что понял, что использовал E1 в качестве значения поиска, но в вашей ситуации измените это на ячейку, применимую к вам. Кроме того, диапазоны данных являются предположением и могут нуждаться в изменении.
Чтобы отразить ваши данные, как указано ниже: условия поиска указаны от H2 до I500, а пул данных — от A2 до F1465, где я предполагаю, что отображаемые данные результата должны поступать из столбца B: =INDEX(B2:B1465,MATCH(1,MMULT(--(H2:I500=A1),TRANSPOSE(COLUMN(H2:I500)^0)),0))
Комментарии:
1. Спасибо! я не смог заставить эту формулу работать, как бы это ни было похоже на элегантный подход .. можете ли вы сказать мне, какие темы мне нужно понять, чтобы придумать подобное решение?
2. Комбинация сопоставления индексов часто является лучшей практикой, чем ВПР, потому что возможность использовать несколько условий. Для этого используйте
INDEX(Result_Range,MATCH(1,(criteria_1)*(criteria_2)*(etc.),0)), чтобы номер строки, удовлетворяющий всем условиям, был TRUE (=1).3. Каков диапазон ваших данных для вашей первой таблицы и что для вашей второй?
4. Мои условия поиска от H2 до I500, а мой пул данных представляет собой таблицу. От A2 до F1465. Это был один из вопросов, когда я вводил таблицу для каждого имени, что привело к 0
5. Я обновил свой ответ до заданных диапазонов. Дайте мне знать, если это сработает (не забудьте ввести формулу в виде массива, как описано).