Формула Excel: Отримайте n-ту відповідність за допомогою INDEX / MATCH -

Загальна формула

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

Резюме

Щоб отримати декілька значень, що збігаються, із набору даних за допомогою формули, ви можете використовувати функції IF та SMALL, щоб визначити номер рядка кожного збігу та повернути це значення до INDEX. У наведеному прикладі формула в I7 має вигляд:

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

Де названі діапазони - це amts (D4: D11), id (I3) та id (C4: C11).

Зверніть увагу, що це формула масиву, і її потрібно вводити за допомогою Control + Shift + Enter.

Пояснення

По суті, ця формула - просто формула INDEX, яка отримує значення в масиві в певній позиції. Значення n подається у стовпці H, і вся "важка" робота, яку робить формула, полягає у визначенні рядка, з якого слід отримати значення, де рядок відповідає "n-му" збігу.

Функція IF виконує роботу з з’ясування того, які рядки містять збіг, а функція SMALL повертає n-те значення з цього списку. Усередині IF логічний тест:

ids=id

що дає цей масив:

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

Зверніть увагу на збіги ідентифікатора клієнта на 1-й та 4-й позиціях, які відображаються як TRUE. Аргумент "value if true" у IF створює список відносних номерів рядків із таким виразом:

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

який створює цей масив:

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

Потім цей масив "фільтрується" за результатами логічного тестування, і функція IF повертає такий результат масиву:

(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

Помилки обробки

Як тільки для даного ідентифікатора більше не буде збігів, функція SMALL поверне помилку #NUM. Ви можете впоратися з цією помилкою за допомогою функції IFERROR або додавши логіку для підрахунку збігів та перервати обробку, коли число в стовпці H перевищує кількість збігів. Наведений тут приклад показує один підхід.