Вернуть ненулевое печенье и считать

Question

У меня есть две таблицы в моей книге. Первый рабочий лист называется SalesPerson и должен содержать краткое описание продавца, введенного в ячейку B1, а также тип и количество проданных файлов cookie. Макет выглядит так:
Имя: Джордж
Печенье Продано: Количество:

Затем у меня есть вторая рабочая таблица под названием «Данные о продажах», в которой указаны каждый продавец и тип файлов cookie, а также количество, которое продал этот человек. Макет как то так

.......Choc Chip....Vanilla....Lemon....etc
George  4            12          0
Bob                   3
Jerry                 4          1

Допустим, например, в ячейку B1 было введено имя George, под заголовками Cookies Sold и Quantity (которые начинаются с A3 и B3). Я бы хотел вернуть следующее

Choc Chip     4
Vanilla       12

Есть ли формула Excel, которая может сделать это? Я мог бы использовать VLOOKUP или комбинированный метод Index(Match()) если бы SalesPerson содержал все возможные куки-файлы и просто просматривал проданное количество, но так как у него нет никакой опции, я должен вернуть ОБА куки-файлы и счетчик, где счет > = 1 на мой лист SalesPerson?

Я думал об использовании PIVOT TABLE но это только помогает развернуть данные, а не возвращать данные на мой лист SalesPerson, и я также рассмотрел использование функции VLOOKUP() , но я мог получить только это, чтобы вернуть одно значение не как тип пирога и количество.

РЕДАКТИРОВАТЬ
Я мог бы использовать =INDEX(A1:D6, 4, 3) чтобы вернуть счет, но как я мог одним и тем же махом вернуть круг?

Я знаю, что INDEX()MATCH()MATCH() работает, когда у вас есть как вертикальные, так и горизонтальные значения поиска, но можно ли это транспонировать для возврата горизонтального значения, а также возвращаемого значения?

Answer 1

Сначала давайте получим список файлов cookie. Нам нужно использовать формулу массива. В этом случае я обращаюсь к формуле AGGREGATE() внутри INDEX. Я положил "Джордж" в A8 с данными в A1:D4:

=IFERROR(INDEX($B$1:$D$1,AGGREGATE(15,6,(COLUMN($B$2:$D$4)-1)/(($A$2:$A$4=$A$8)*($B$2:$D$4<>0)*($B$2:$D$4<>"")),ROW(1:1))),"")

Затем скопируйте эту формулу на достаточное количество строк, чтобы охватить все возможные длины списка.

Затем я использовал стандартный INDEX/MATCH/MATCH, чтобы найти числа:

=IF(A9<>"",INDEX($A:$D,MATCH($A$8,$A:$A,0),MATCH($A9,$1:$1,0)),"")

И снова скопировал то же количество строк.