Vlookup, чтобы проверить все значения, прежде чем возвращать значение?

Question

У меня есть этот проект, я должен проверить значения с другого листа и скопировать значение ячейки рядом с ним.

Лист 1

Billy
Paul
Mike
Jesse

Лист 2

Billy |
Paul | X
Jesse | 
Billy | X
Mike | X

Я работаю над Листом 1, получая значения из Листа 2.

Мне просто нужно вернуть значение в Col 2, у меня оно работает с = IFERROR(VLOOKUP(B148, LateReports!В: С, 2,0), "-")

Но с Билли, не имеющим X в первый раз, он останавливает поиск и возвращается пустым. Как проверить весь список, сравнить значения ячеек и, если он равен "X", вернуть его?

Answer 1

Вы можете использовать Index(Match), чтобы "продолжить поиск". Он вернет значение, как VLOOKUP.

{=INDEX(Sheet2!$A$1:$C$5,MATCH(1,(Sheet2!$A$1:$A$5=Sheet1!A1)*(Sheet2!$B$1:$B$5="X"),0),3)}

Объявите область массива для INDEX, Sheet2!$ A $ 1:$ C $ 5, включая столбец даты.

Вызовите MATCH внутри INDEX, используя "1" в качестве значения для поиска, а "1" - "ИСТИНА"

(Лист2!$ A $ 1:$ A $ 5 = Лист1!А1) будет равно 1, и так будет (Лист2!$ B $ 1:$ B $ 5 = "X"), который возвращает 1, когда мы умножаем их, возвращая значение "ИСТИНА".

Мы закрываем наш MATCH с помощью "0", что означает, что мы хотим получить точное совпадение между "1" и нашим вторым аргументом, который мы умножили.

Закройте INDEX столбцом массива, на который вы хотите сослаться, в данном случае "3" для столбца C, который должен содержать дату, указанную вами в комментарии.

НАИБОЛЕЕ ВАЖНО, вместо того, чтобы просто нажимать ENTER, используйте CTRL+SHIFT+ENTER, что поместит эти фигурные скобки вокруг вашей формулы

Вот как должны выглядеть листы Excel.

Sheet1- Two columns
Billy|INDEX(MATCH)
Paul|drag your INDEX(MATCH)
Mike|drag your INDEX(MATCH)
Jesse|drag your INDEX(MATCH)

Sheet2- Three Columns including Date in "C"
Billy||42450
Paul|X|42450
Jesse| |42451
Billy|X|42452
Mike|X|42452

Answer 2

Вы можете отсортировать таблицу поиска по полковнику А, а затем по убыванию полковника В. В этом случае VLOOKUP сначала найдет "Билли" с буквой X.

Answer 3

Как насчет (при условии, что ваши столбцы Sheet2 находятся в столбцах A и B, и что ваш столбец Sheet1 находится в столбце A), введите это в столбце B Sheet1:

=IF(COUNTIFS(Sheet2!A:A,A1,Sheet2!B:B,"X")>0,"I found it!","I didn't find it")

Это работает, если вы знаете, что хотите искать именно "X", а затем подсчитывает, сколько раз он находит два теста (а) это Билл (потому что его имя в А1; когда вы копируете формулу вниз, он сделает A2, A3 и т.д., Которые будут Пол и Майк и т.д.), И (б) есть X.

Однако, если вы не знаете , как это называется "X", и вы хотите узнать, что это называется, то у вас есть проблема , потому что как же Excel знать , какие строки , чтобы дать вам? Пустая строка? Строка с "X" или "Y" и т.д.?

Если вы ищете не "х", а какую-либо непустую строку, сделайте то же самое, что и выше, но

=IF(COUNTIFS(Sheet2!A:A,A1,Sheet2!B:B,"<>""")>0,"I found it!","I didn't find it")

который ищет что-то, что не равно ничему ("" ничто, <> "" не равно ничему, "<>" "" не равно нулю в кавычках, чтобы это работало.

Я думаю, что они все равно работают, но они только говорят вам, есть ли что-то, а не то, что есть :-)

---

Альтернативный подход - создать сводную таблицу (Insert -> PivotTable) и отфильтровать пробелы, используя фильтр, и, возможно, поместить столбцы Name и X в метки строк. Затем вы можете выполнить VLOOKUP для столбцов сводной таблицы, хотя это все равно не поможет, если у вас есть несколько непустых записей для каждого человека, так как он по-прежнему не будет знать, что искать.

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