Формула Excel: получить n-е совпадение с помощью INDEX / MATCH -

Общая формула

(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))

Резюме

Чтобы извлечь несколько совпадающих значений из набора данных с помощью формулы, вы можете использовать функции ЕСЛИ и НАИМЕНЬШИЙ, чтобы вычислить номер строки каждого совпадения и передать это значение обратно в ИНДЕКС. В показанном примере формула I7 имеет следующий вид:

(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))

Где именованные диапазоны - это amts (D4: D11), id (I3) и ids (C4: C11).

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

Объяснение

По сути, эта формула представляет собой просто формулу ИНДЕКС, которая извлекает значение в массиве в заданной позиции. Значение n указывается в столбце H, и вся «тяжелая» работа, которую выполняет формула, - это определение строки, из которой следует извлечь значение, где строка соответствует «n-му» совпадению.

Функция ЕСЛИ выполняет работу по выяснению, какие строки содержат совпадение, а функция НАИМЕНЬШИЙ возвращает n-е значение из этого списка. Внутри IF логический тест:

ids=id

что дает этот массив:

(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)

Обратите внимание на совпадение идентификатора клиента на 1-й и 4-й позициях, которые отображаются как ИСТИНА. Аргумент «значение, если истина» в IF создает список относительных номеров строк с этим выражением:

ROW(ids)-ROW(INDEX(ids,1,1))+1

который производит этот массив:

(1;2;3;4;5;6;7)

Затем этот массив "фильтруется" по результатам логического теста, и функция ЕСЛИ возвращает следующий результат массива:

(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)

Обратите внимание, что у нас есть действительные номера строк для строк 1 и 2.

Затем этот массив обрабатывается SMALL, который настроен на использование значений в столбце H для возврата «n-го» значения. Функция SMALL автоматически игнорирует логические значения TRUE и FALSE в массиве. В итоге формулы сводятся к:

=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125

Обработка ошибок

Если совпадений для данного идентификатора больше нет, функция МАЛЕНЬКИЙ возвратит ошибку # ЧИСЛО. Вы можете обработать эту ошибку с помощью функции ЕСЛИОШИБКА или путем добавления логики для подсчета совпадений и прерывания обработки, когда число в столбце H больше, чем количество совпадений. Пример здесь показывает один подход.