Отфильтруйте значения по категориям в новую таблицу с помощью vlookup

Question

У меня есть следующие данные, которые содержат список агентов и их соответствующего менеджера:

Мне нужно отфильтровать агентов по менеджерам и поместить их в новую таблицу для каждого менеджера?

Я пробовал использовать vlookup, как видно на картинке, но, похоже, как только функция найдет значение, оно будет придерживаться этого значения.

Синие таблицы - это результат, который я хочу получить. Я предполагаю, что это должна быть функция для каждой новой таблицы - это нормально!

Я мог бы попробовать использовать VBA с циклом for, но я решил, что не буду изобретать велосипед, если это возможно.

Спасибо!

Answer 1

VLOOKUP() всегда найдет первое вхождение lookup_value.

INDEX(array, row_num,[column_num]) может перечислить агентов, если вы дали ему row_num каждого из них, когда он был заполнен.

Начнем с получения массива (списка) номеров строк Агентов для Марка. Это выражение

IF($E$2:$E$9=I$1,ROW($E$1:$E$8))

проверяет, где столбец E равен I$1 (Mark), и где бы он ни находился, он возвращает число из списка номеров строк для строк с 1 по 8. В позициях, где столбец E не равен Mark, выражение возвращает False . Таким образом, фактический массив, который дает это выражение: {1,FALSE,3,FALSE,5,FALSE,7,FALSE} . Обратите внимание, что число, возвращаемое для каждого из агентов Марка, фактически на единицу меньше номера строки.

Далее мы хотим перечислить эти числа по мере заполнения формулы, игнорируя значения FALSE . SMALL(array, k) возвращает k-е наименьшее значение из array . По мере заполнения ROW(I1) увеличивается, а номера перечислены по порядку:

SMALL(IF($E$2:$E$9=I$1,ROW($E$1:$E$8)),ROW(I1))

Теперь мы можем использовать это выражение для row_num в функции INDEX() чтобы получить имена Агентов Марка:

=INDEX($F$2:$F$9,SMALL(IF($E$2:$E$9=I$1,ROW($E$1:$E$8)),ROW(I1)))

Это выражение возвращает значение в F2:F9 соответствующее номеру, указанному в SMALL() . Таким образом, по мере заполнения он перечисляет имена агентов Марка. Вы можете использовать эту формулу для перечисления агентов, но см. Примечание ниже.

Если он заполнен дальше, чем количество Агентов, которые есть у Марка, он даст #N/A! ошибка. Их можно преобразовать в пробелы, обернув всю формулу в IFERROR():

=IFERROR(INDEX($F$2:$F$9,SMALL(IF($E$2:$E$9=I$1,ROW($E$1:$E$8)),ROW(I1))),"")

Эта формула, заполненная справа от I2, дает результаты, показанные ниже.

Обратите внимание, что это формула массива, поэтому ее нужно вводить с помощью CTRL Shift Enter, а не просто Enter.

Это довольно многословное объяснение формулы, но я надеюсь, что это поможет. Удачи.