Excel VLOOKUP по 2 колонки

Question

У меня проблема с функцией VLOOKUP. Вот мои отчеты:

sheet1

id_1  id_2 count
28273 15   5
31866 19   4
2827  315  5
...

sheet2

id_1  id_2 count
2827  315  155
141   1064 555
...

По сути, я хочу добавить count из sheet2 в соответствующую строку в sheet1 . Но я столкнулся с проблемой: для сравнения значений я объединяю их: поэтому для первой строки в sheet1 я использую id_1&id_2 в значении VLOOKUP . Проблема в том, что для 28273 and 15 из sheet1 я получил 2827 and 315 из sheet2 . Так что это не точное совпадение, которое мне нужно.

Какую формулу я должен использовать, чтобы получить значение count из sheet2 для соответствующих значений, например:

sheet1

id_1  id_2 count count2
2827  315  5     155
...

Answer 1

Если два значения ключа создают уникальный ключ, вы можете использовать SUMIFS и пропустить формулы массива:

=SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,B2)

Это вернет 0 для любого, у которого нет совпадения со значениями ключа, но вы можете использовать пользовательский формат #;#;;@ для отображения пустой ячейки, когда 0. Или #;#;"No Match";@ для возврата No Match когда 0

Answer 2

В комментариях было предложено самое простое решение использовать какой-то уникальный символ между столбцами для создания вспомогательного столбца для обоих листов, например: id_1&"."&id_2

Итак, первый - я создал вспомогательные столбцы для sheet2 как id_1&"."&id_2 .

Тогда у меня на sheet1 столбцы:

id_1 id_2 count1 count2
...

На sheet2:

id_1 id_2 helper count2
...

Затем я сравниваю столбцы с помощью VLOOKUP в sheet1:count2:count2 : VLOOKUP VLOOKUP(sheet1!id_1&"."&sheet1!id_2;sheet2!helper:count2;2;FALSE)

Answer 3

fixer1234 предоставил лучшее решение в комментариях, которые уже опубликовал Саймон.

У меня тупо сложное решение, которое все еще работает, если вы не можете добавить столбцы. Требуется, чтобы каждый идентификатор был уникальным, если один и тот же идентификатор встречается дважды, он не будет работать должным образом.

IF(MATCH(A2;Sheet2!A:A;0)=MATCH(B2;Sheet2!B:B;0);INDEX(Sheet2!C:C;MATCH(A2;Sheet2!A:A;0));0)

Пояснение:INDEX и MATCH чем-то похожи на компоненты VLOOKUP . MATCH находит число, а INDEX находит значение этого числа в выбранном вами диапазоне. Я проверяю, равно ли число строк первого id_1 равно числу первых id_2 и если да, то я делаю то, что по сути является более быстрым и более гибким VLOOKUP .

Answer 4

В своем комментарии вы сказали, что предпочитаете не использовать конкатенацию или вспомогательный столбец. Вот способ сделать это.

Я использовал твой пример. Лист2:

Лист1:

Я поместил формулу в ячейку с совпадением, потому что я не знал, как вы хотите справиться с любыми потенциальными несоответствиями (вы можете заключить это в IFERROR и отобразить пробел). Я протестировал его на всех строках, и он правильно обрабатывает ваше условие строки 1 (возвращает ошибку на основе ограниченных данных и отсутствия обработки ошибок). Формула в D4:

=INDEX(Sheet2!$C$2:$C$6,MATCH(1,(Sheet2!$A$2:$A$6=A4)*(Sheet2!$B$2:$B$6=B4),0))

Обратите внимание на изображение, которое окружено фигурными скобками. Это потому, что это формула массива и должна быть подтверждена с помощью Ctrl+Shift+Enter вместо просто Enter.

INDEX извлекает соответствующий счетчик из Sheet2. Он выполняет логический тест, который будет истинным, только если оба условия выполняются, поэтому он сравнивает 1 (True) с результатами для каждой строки. Он сравнивает значение столбца A листа Sheet2 со значением столбца A в Sheet1, а значение столбца B столбца Sheet2 со значением столбца B в Sheet1. Если он находит запись, в которой значения столбца A и B соответствуют значениям A и B текущей строки, это совпадает.

Если у вас может быть условие, что не каждая строка в Sheet1 имеет счетчик для извлечения на Sheet2, простой способ отобразить пробел на Sheet1 в столбце Count2 - это обернуть формулу следующим образом (все еще формула массива):

=IFERROR( INDEX(Sheet2!$C$2:$C$6,MATCH(1,(Sheet2!$A$2:$A$6=A4)*(Sheet2!$B$2:$B$6=B4),0)) ,"")

Answer 5

=INDEX(SHEET2!C:C,aggregate(14,6,row(Sheet2!A$2:A$10)/((Sheet2!A$2:A$10=A2)*(Sheet2!B$2:B$10=B2)),1))

Приведенная выше формула использует AGGREGATE, которая выполняет операции, подобные массиву, фактически не являясь массивом, по крайней мере, для некоторых функций, таких как 14 и 15. 14 указывает AGGREGATE сортировать результаты от наибольшего к наименьшему, 15 сортирует от наименьшего к наибольшему. 6 говорит AGGREGATE игнорировать результаты, которые приводят к ошибкам.

ROW(SHEET2!A$2:A$10)

Эта часть дает AGGREGATE номер строки, которая в данный момент оценивается.

((Sheet2!A$2:A$10=A2)*(Sheet2!B$2:B$10=B2))

Эта часть является двумя условиями, которые должны быть выполнены, чтобы AGGREGATE не получил ошибку. * Действует как функция AND. когда оба или один из них имеют значение false, результат в результате равен 0, что вызывает ошибку деления на 0. Если оба результата верны, то это приводит к 1, и номер строки не изменяется путем деления на один. Вы получите список отфильтрованных результатов, которые соответствуют вашим критериям.

1 говорит AGGREGATE, что нужно возвращать результаты 1 из отсортированного списка. Это означает, что при наличии нескольких строк, соответствующих вашим критериям, последняя соответствующая строка возвращается для функции 14 AGGREGATE, а первая строка возвращается для функции 15.

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

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

Настройте диапазоны в соответствии с вашими данными.

Вы не указали, что вы хотели в результате, когда что-то не было найдено в списке. В настоящее время он вернет ошибку. Чтобы привести это в порядок, вы можете обернуть все это в функцию IFERROR и сделать так, чтобы она выглядела следующим образом:

=IFERROR(INDEX(Sheet2!C:C,AGGREGATE(14,6,ROW(Sheet2!A$2:A$10)/((Sheet2!A$2:A$10=A2)*(Sheet2!B$2:B$10=B2)),1)),"NO MATCH")