СОЧЕТАНИЕ ФУНКЦИЙ INDEX + MATCH, КОГДА ОБЫЧНЫЙ VLOOKUP НЕ РАБОТАЕТ
К сожалению, функция ВПР (VLOOKUP) не работает, когда искомые значения в исходной таблице находятся не в первом столбце.
Но вы можете воспользоваться сочетанием функций ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX).
Функция ИНДЕКС возвращает элемент из списка по его порядковому номеру.
А порядковый номер вы определяете с помощью ПОИСКПОЗ.
Пример:
На скриншоте мы подтягиваем тематику по названию книги, хотя названия находятся не в первом столбце искомой таблицы.
КЕЙС С ДВССЫЛ (INDIRECT) И ПОИСКПОЗ (MATCH)
Наша таблица ниже. Давайте напишем формулу ДВССЫЛ (INDIRECT) со стилем ссылок R1C1, которая по выбранному типу оплаты и месяцу будет возвращать нам значение из этой таблицы.
Задачей будет узнать, сколько мы заплатили за дизайн в октябре 2016.
Для использования ДВССЫЛ() нам нужно получить адрес вида R1C1. Для этого найдем номер строки с типом оплаты «дизайн» и номер столбца «01-10-16» в таблице выше.
Искать будем с помощью формулы ПОИСКПОЗ (MATCH). Начнем с номера строки и заодно разберемся, как работать с этой формулой:
Давайте разберем аргументы формулы:
1) поисковый запрос (А13);
2) диапазон, в котором будет производиться поиск (А1:A9);
3) метод поиска (для поиска точного значения, как в нашем случае, нужно ввести 0).
Формула возвращает 8, это номер типа оплаты «дизайн» в диапазоне A1:A9, а вместе с этим и фактический номер строки с этим типом оплаты на листе, так как наш диапазон начинается с первой строки (A1:A9):
А вот так будет выглядеть формула ПОИСКПОЗ для поиска номера столбца: логично, что диапазон для поиска уже другой — это строка нашей таблицы, в которой перечислены месяцы:
Результат — 5, то есть положение «01-10-16» в диапазоне А1:G1 — 5-й элемент слева направо. А еще 5 будет фактическим номером столбца, ведь диапазон для поиска начинается с первого столбца листа (A1:G1):
После применения обеих формул мы получим строку 8 и столбец 5, это и будет адресом искомой ячейки.
Теперь объединим две формулы ПОИСКПОЗ и добавим к ним буквы R и C, чтобы прийти к виду R8C5; для этого используем символ &, а текст обязательно возьмем в кавычки:
Добавим получившееся в ДВССЫЛ с аргументом 0:
Не забудьте аргумент 0 в конце формулы; если его опустить, то формула работать не будет, так как по умолчанию аргумент равен 1, а это стиль адреса A1:
У нас получилось! Формула возвращает 11 250, это оплата дизайна за октябрь 2016.
Можно менять вводные — все продолжит работать:
КЕЙС «ДОБАВЛЯЕМ К ИНДЕКС И ПОИСКПОЗ ФУНКЦИЮ НАИБОЛЬШИЙ (LARGE) И НАХОДИМ N-Ю ПО ПРОДАЖАМ КНИГУ В СПИСКЕ»
Допустим, вам нужно определить n-ю по продажам книгу в списке и сделать это без применения фильтра и сортировки, а с помощью формулы, которая подтянет название по номеру, указанному в отдельной ячейке:
Функция LARGE (НАИБОЛЬШИЙ) выдает n-е по величине значение в диапазоне. У нее два аргумента:
LARGE(массив, n)
Есть у нее и функция-близнец SMALL (НАИМЕНЬШИЙ), которая работает аналогично, но возвращает n-е наименьшее значение.
Итак, чтобы получить третье значение, укажем в качестве первого аргумента LARGE диапазон, а второго — 3:
Как быть, если мы хотим вводить в отдельную ячейку порядковый номер, а не менять его внутри функции каждый раз? И получать не само значение, а соответствующее ему название книги (то есть ответ на вопрос «Какая книга n-я по продажам?»)?
Воспользуемся сочетанием INDEX и MATCH, описанным выше:
- в функции MATCH с помощью LARGE определим порядковый номер строки — найдем, в какой строке находится n-й элемент столбца со значениями;
- с помощью INDEX подтянем из соседнего столбца название книги:
=INDEX(названия_книг,MATCH(LARGE(значения,n),значения,0))
=ИНДЕКС(названия_книг,ПОИСКПОЗ(НАИБОЛЬШИЙ(значения,n),значения,0)).
В качестве n выступит не фиксированное число, а ссылка на ячейку, в которую мы будем вносить порядковый номер:
=INDEX(A3:A14,MATCH(LARGE($B$3:$B$14,$D$3),$B$3:$B$14,0))
=ИНДЕКС(A3:A14,ПОИСКПОЗ(НАИБОЛЬШИЙ($B$3:$B$14,$D$3),$B$3:$B$14,0))
Теперь достаточно поменять номер в ячейке D3, чтобы автоматически получить в E3 название соответствующей книги:
ЖЕРЕБЬЕВКА ФУТБОЛЬНОГО КУБКА: ФУНКЦИИ ИНДЕКС И СЛУЧМЕЖДУ
Рассмотрим, как провести жеребьевку кубка, выбрав случайную пару команд из двух разных лиг.
Задача состоит из двух простых этапов:
- Выбрать случайные числа от 1 до N, где N — количество команд в лиге (функция СЛУЧМЕЖДУ);
- Вернуть из списка названия команд, соответствующие этим номерам (функция ИНДЕКС).
Формула для одного из клубов будет выглядеть следующим образом:
=INDEX(список_клубов; RANDBETWEEN(1;N))
=ИНДЕКС(список_клубов; СЛУЧМЕЖДУ(1;N)).
Можно отображать названия клубов в отдельных ячейках, как на скриншоте выше. Еще один способ — сформировать сразу пару с помощью текстовых функций:
=INDEX(список_клубов1; RANDBETWEEN(1;N))&" : "&INDEX(список_клубов2; RANDBETWEEN(1;M))
=ИНДЕКС(список_клубов1; СЛУЧМЕЖДУ(1;N))&" : "&ИНДЕКС(список_клубов2; СЛУЧМЕЖДУ(1;M)).
