Багаторазовий впр (vlookup)

Маємо список замовлень з номерами і назвами товарів. Хотілося б, для прикладу, витягувати з таблиці за номером замовлення усі товари, які в нього входять. Приблизно так:

Чудова функція ВПР (VLOOKUP) в такій ситуації допоможе тільки частково, тому що вміє витягувати дані тільки по першому знайденому відповідності, тобто дасть нам тільки Яблука. Для знаходження та вилучення з таблиці всіх найменувань краще використовувати формулу масиву. Ось таку:

= ІНДЕКС ($ B $ 2: $ B $ 16; НАЙМЕНШИЙ (ЯКЩО ($ E $ 2 = A2: A16; СТРОКА (B2: B16) -1; ""); СТРОКА () -5))

Її треба ввести в такий спосіб:

  1. виділити осередки, куди повинні виводитися результати (в нашому прикладі - це діапазон D6: D20)
  2. ввести (скопіювати формулу в перший осередок) діапазону
  3. натиснути Ctrl + Shift + Enter

Віднімання одиниці в фрагменті СТРОКА (B2: B16) -1 робиться через шапки таблиці. З тієї ж причини для компенсації зсуву результуючого діапазону щодо вихідного віднімається число п'ять у фрагменті СТРОКА () - 5

Щоб приховати помилку # ЧИСЛО. яка буде з'являтися в незаповнених комірках результуючого діапазону D6: D20 можна використовувати функції перевірки помилок ЯКЩО і ЕОШ, замінивши нашу формулу трохи складнішою:

= ЕСЛИ (ЕОШ (ІНДЕКС ($ B $ 2: $ B $ 16; НАЙМЕНШИЙ (ЯКЩО ($ E $ 2 = A2: A16; СТРОКА (B2: B16) -1; ""); СТРОКА () - 5))); " "; ІНДЕКС ($ B $ 2: $ B $ 16; НАЙМЕНШИЙ (ЯКЩО ($ E $ 2 = A2: A16; СТРОКА (B2: B16) -1;" "); СТРОКА () - 5)))

= ЕСЛІОШІБКА (ІНДЕКС ($ B $ 2: $ B $ 16; НАЙМЕНШИЙ (ЯКЩО ($ E $ 2 = A2: A16; СТРОКА (B2: B16) -1; ""); СТРОКА () - 5)); "")

В англомовній версії Excel ці функції будуть виглядати так:

Посилання по темі