vlookup с использованием двух имен столбцов на разных листах

Question

У меня есть одна рабочая тетрадь с двумя отдельными рабочими листами. Я хочу знать, отображаются ли значения, которые отображаются на листе B, также на листе A, если да, я хочу вернуть "ДА". Если нет, я хочу вернуть "НЕТ". На основании столбцов А и В.

На листе А у меня есть следующий набор данных:

   NAME             DOB
1  Bob Builder   1/1/2001 
2  Patrik Str    2/2/2001
3  Thunder Ct    3/3/2001
4  peter Grif    4/4/2001

На рабочем листе B у меня есть следующий набор данных:

   NAME            DOB
1  Bob Builder   1/1/2001 
2  Patrik Str    2/2/2001
3  Thunder Ct    3/3/2001
4  peter Grif    4/4/2001
5  Bob Builder   8/8/2011 
6  Patrik Str    2/25/2001

Я пытался =IFERROR(IF(MATCH(A1,Sheet1!$A:$A,0),"yes",),"no") но он ищет только столбец A, а не B. Это прекрасно работает только потому, что он смотрит на столбец A, но есть и другие ученики, у которых тот же имя, но разные DOB.

Можно ли использовать функцию vlookup или match в Excel, сравнивая заголовок имени столбца, например, name и DOB вместо A, B.

Answer 1

Вы можете использовать эту формулу:

=IF(SUMPRODUCT(--('Worksheet A'!$A$2:$A$5='Worksheet B'!A2)*(--'Worksheet A'!$B$2:$B$5='Worksheet B'!B2))=1,"Yes","No")

$A$2:$A$5 массив имени в первом рабочем листе изменяет его до последней ячейки в столбце « Name и $ исправит его, чтобы вы могли перетащить его без изменения
Один A2 - это имя во втором листе
$ B $ 2:$ B $ 5 массив DOB на первом листе меняет его до последней ячейки в столбце DOB и $ исправит это, так что вы можете перетаскивать его без изменения
B2 соответствующая дата A2 во втором листе
Sumproduct приведет к 1, когда и A2 и B2 совпадают с соответствующими данными
Если 1 - результат, он выдаст "Да", если нет совпадений - "Нет".
Вы можете перетащить формулу вниз

=IF(SUMPRODUCT(--(Name=A2)*(--DOB=B2))=1,"Yes","No")
Если вы используете Определение имени на worksheet A и присваиваете Name столбцу Имени без заголовка
Также DOB для столбца DOB на worksheet A без заголовка.
Вы можете использовать приведенную выше формулу и перетащить ее

Если вы предпочитаете использовать Match используйте следующее:

=IF(ISNA(MATCH(1,(--(Name=A2)*(--(DOB=B2))),0)),"No","Yes")
и одновременно нажмите Ctrl + Shift + Enter (Array Formula)
Вы также можете перетащить его

Answer 2

Сделайте третий столбец на каждом листе с формулой

= A2 & " " & B2

Используйте этот столбец для сопоставления.

Answer 3

Используя таблицы и структурированные ссылки, это может быть довольно просто. Для каждой таблицы данных: Insert ► Tables ► Table

И этот метод также позволяет ссылаться на заголовки столбцов по имени.

Имена по умолчанию будут Table1 и Table2 - первая таблица, которую вы создадите, будет 1 , а вторая 2 , но вы можете переименовать в Formula ► Name Manager

Затем для Table2 в C1 добавьте заголовок столбца (например, "В таблице 1". Должен появиться третий столбец таблицы.

C2: =IF(SUMPRODUCT(([@NAME]=Table1[NAME])*([@DOB]=Table1[DOB])),"YES","NO")

Формула будет автоматически распространяться вниз и даст вам ваши результаты.

Другой вариант, который работает при условии, что столбец NAME - это столбец 1, будет выглядеть так:

C2: =IF(VLOOKUP([@NAME],Table1,2,FALSE)=[@DOB],"YES","NO")

При структурированных ссылках в таблицах @NAME ссылается на запись в той же строке формулы под заголовком столбца "NAME"; Table1[NAME] относится ко всему столбцу (кроме заголовка) в Таблице1. [NAME] само по себе будет ссылаться на весь столбец в той же таблице.

Другое преимущество использования таблиц состоит в том, что таблица (и ссылки) автоматически расширяются / сжимаются при добавлении / удалении соседних строк или столбцов.