Найти соответствующее значение в строке и вернуть имя столбца II

Question

Мой qeuration похож, но отличается от Найти совпадающее значение в строке и вернуть имя столбца?

У меня есть таблица с двумя вкладками:

1. На вкладке A есть один столбец, который представляет собой список адресов электронной почты (на этой вкладке есть много других столбцов)
2. Вкладка B имеет 4 столбца, каждый из которых имеет адреса электронной почты из разных групп. Каждый столбец имеет уникальные адреса электронной почты (между столбцами нет дупликов) Таким образом, группа 1 имеет 50 электронных писем в столбце A, а группа 2 имеет 100 электронных писем в столбце B и т.д.

Я хотел бы вставить формулу в новый столбец на вкладке A, который просматривает электронные письма в каждом из 4 столбцов на вкладке B и сравнивает их с адресом электронной почты на вкладке A. Если совпадение найдено, напишите заголовок столбца.

Большое спасибо за любую помощь> ;-) Я открыт для любых идей о том, как настроить это, чтобы облегчить ....

Answer 1

И вы можете иметь еще более короткий с SUMPRODUCT:

=INDEX(Sheet2!$A$1:$D$1,SUMPRODUCT((A2=Sheet2!$A:$D)*1*{1,2,3,4}))

И я также заметил, что если совпадений не найдено (как и в случае других формул, опубликованных перед этим ответом), он вернет индекс столбца для самой формулы (т. Е. Если формула находится в столбце B, он вернет имя заголовка столбца B). Один из способов избежать этого - использовать IF следующим образом:

=INDEX(Sheet2!$A$1:$D$1,IF(SUMPRODUCT((A2=Sheet2!A2:D8)*1)=0,NA(),SUMPRODUCT((A2=Sheet2!A2:D8)*1*{1,2,3,4})))

Это немного длиннее ^^;

Answer 2

Следующая формула массива должна работать. Введите его в ячейку строки 2 нового столбца на вкладке A, а затем скопируйте его в нижнюю часть списка сообщений электронной почты на вкладке A.

  =INDEX(Sheet2!$A$1:$D$1,1,SUM((A2=Sheet2!$A$2:$A$50)*1+(A2=Sheet2!$B$2:$B$100)*2+(A2=Sheet2!$C$2:$C$50)*3+(A2=Sheet2!$D$2:$D$50)*4))

Как формула массива, ее необходимо вводить с помощью комбинации клавиш Control - Shift - Enter .

Чтобы разбить части формулы, на мгновение предположим, что во вкладке B есть только один столбец данных электронной почты, например столбец A:

• $A2=Sheet2!$A$2:$A$50 сравнивает электронное письмо в ячейке A2 на вкладке A со всеми электронными письмами в столбце B на вкладке B, получая массив результатов совпадений, таких как {False,True,False...False} Поскольку электронные письма на вкладке B уникальны, в массиве может быть не более 1 True .

• Затем этот массив умножается на 1 - ($A2=Sheet2!$A$2:$A$50)*1 - создать другой массив '{0,1,0 ... 0}', который можно интерпретировать как «Значение в A2 совпадает со значением во второй строке диапазон данных в первом столбце вкладки B.

• Суммирование этого массива дает значение 1, которое используется для выбора значения в первом столбце строки заголовка вкладки B: INDEX($A$1:$D$1,1,SUM(($A2=Sheet2!$A$2:$A$50)*1)) .

• Мы можем выполнить такое же сравнение массивов со столбцами B, C и D на вкладке B, умножив результаты на 2, 3 и 4 соответственно, чтобы показать совпадение во втором, третьем и т.д. Столбце вкладки B.

Answer 3

Вы также можете сделать это с формулой без массива, используя Countif() вместо A2 = Range:

=INDEX(Sheet2!$A$1:$D$1,(COUNTIF(Sheet2!$A:$A,Sheet1!$A2)*1)+(COUNTIF(Sheet2!$B:$B,Sheet1!A2)*2)+(COUNTIF(Sheet2!$C:$C,Sheet1!A2)*3)+(COUNTIF(Sheet2!$D:$D,Sheet1!A2)*4))