MS Excel vlookup возвращает только один экземпляр

Question

У меня есть лист как

       A      |       B     |     C   |
1  Customer 1 |  Product 1  |  $150   |
1  Customer 1 |  Product 1  |  ----   |
2  Customer 1 |  Product 2  |  $50    |
3  Customer 2 |  Product 1  |  $150   |

Идея состоит в том, что столбец C:C извлекает цену из vlookup, но я не хочу добавлять цену, если тот же продукт уже использовался для этого клиента, у меня есть эта формула, которая работает почти, но она исключает для того же клиента даже с другим продуктом. Формула для столбца C.

=IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="","",IF(COUNTIF($A$3:A3,B3)>1,"",VLOOKUP(C3,data_validation!A:B,2)))

PS data_validation - это просто другой лист, где я создаю все свои проверки и использую их для поиска цены на основе номера продукта.

Answer 1

Вот решение, включающее предложение Ли использовать функцию SUMPRODUCT(). Заполните эту формулу вниз от C3:

=IF(SUMPRODUCT((A$3:A3=A3)*(B$3:B3=B3))>1,"",VLOOKUP(B3,E$4:F$5,2))

Как это работает: выражения A $ 3: A3 = A3 и B $ 3: B3 = B3 возвращают массивы значений True/False, которые увеличиваются по мере заполнения формулы. Например, в C6 A $ 3: A6 = A6 равно {False; False; False; True}, поскольку последняя позиция является единственной ячейкой в диапазоне, равном A6. Аналогично, B $ 3: B6 = B6 равно {False; False; True; True}, поскольку последние две позиции соответствуют B6.

Теперь умножение этих двух массивов преобразует значения True/False в 1 и 0 и выполняет эквивалент логического AND(). Результатом умножения (в C6) является массив {0; 0; 0; 1}. SUMPRODUCT () складывает элементы этого массива, возвращая 1.

Теперь функция IF() возвращает результат VLOOKUP, потому что результат SUMPRODUCT() не> 1.

Если посмотреть на формулу в C4, то A $ 3:A4 = A4 равно {True; True}, а B $ 3:B4 = B4 также равно {True; True}, поэтому умножение дает {1; 1}, а SUMPRODUCT() возвращает 2 и IF() возвращает пробел.

РЕДАКТИРОВАТЬ, чтобы показать результаты:

Я поместил таблицу поиска на тот же лист, что и данные. Вы можете отредактировать уравнение, чтобы обратиться к вашей справочной таблице.

Примечание. Если этот ответ окажется полезным, нажмите на серую галочку в левом верхнем углу, чтобы принять ответ и удалить его из очереди без ответа. Благодарю.