Поиск слева с помощью VLOOKUP
В этом руководстве мы обсудим, как использовать функцию VLOOKUP для поиска информации, указанной в левых столбцах Excel, о заданном элементе, который находится справа. Мы знаем, что функция VLOOKUP не может искать значения слева, поэтому для выполнения этой задачи нам придется воспользоваться помощью функции CHOOSE .
Как выполнить поиск слева с помощью VLOOKUP?
Чтобы заполнить информацию о продукте 30001, которая указана в крайнем правом столбце данных, как показано на скриншоте выше, вы можете использовать функцию CHOOSE для переупорядочивания таблицы – «переместить» крайний правый столбец со значением поиска в самое левое положение. Затем вы сможете использовать обычную формулу VLOOKUP для получения нужного значения.
Общий синтаксис
=VLOOKUP(lookup_value,CHOOSE({1,2,3,…,N},rangeN,range1,range2,range3,…),column_num,FALSE)
- lookup_value: Значение, которое VLOOKUP использует для определения позиции соответствующей информации. Здесь имеется в виду заданный ID продукта.
- Range1, range2, range3, rangeN: Столбцы ячеек, где указаны lookup_value и другая информация.
- column_num: Номер, указывающий, из какого столбца нужно получить данные. Обратите внимание, что поскольку крайний правый столбец теперь перемещен в самое левое положение из-за функции CHOOSE, чтобы представить 1-й столбец (столбец цвета), вы должны указать column_num как 2; чтобы представить 2-й столбец (столбец размера), вы должны указать column_num как 3…
- range_lookup FALSE: Заставляет VLOOKUP находить только точное совпадение.
Чтобы заполнить информацию о продукте 30001, скопируйте или введите формулы ниже в соответствующие ячейки и нажмите Enter для получения результатов:
Цвет (Ячейка H6)
=VLOOKUP($H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),2,FALSE)
Размер (Ячейка H7)
=VLOOKUP($H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),3,FALSE)
Цена (Ячейка H8)
=VLOOKUP($H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),4,FALSE)
Примечание: Вместо того чтобы вводить ID продукта 30001 в формулы, мы использовали ссылку на ячейку $H$4 (мы добавили знаки доллара к ссылке, чтобы сделать её абсолютной), так как хотим, чтобы формулы были динамическими. Таким образом, мы можем легко получить информацию о других продуктах, просто изменив ID продукта в ячейке h3.
Объяснение формулы
Здесь мы используем следующую формулу в качестве примера:
=VLOOKUP($H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),2,FALSE)
- CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9): Функция CHOOSE переупорядочивает столбцы, перемещая столбец E на первое место, или, другими словами, в самое левое положение в массиве, например, так:
{30001,"White","Large",20;30002,"Black","Large",21;30003,"Blue","Medium",19;30004,"Red","Medium",18;30005,"Yellow","Small",20}.
В табличной форме массив будет выглядеть так: - VLOOKUP($H$4$H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),22,FALSE) = VLOOKUP($H$4$H$4,{30001,"White","Large",20;30002,"Black","Large",21;30003,"Blue","Medium",19;30004,"Red","Medium",18;30005,"Yellow","Small",20},22,FALSE): С 30001, значением поиска в ячейке h3, функция VLOOKUP найдет строку его точного совпадения в новой таблице, созданной функцией CHOOSE, и вернет результат в втором столбце, который является White.
Связанные функции
Функция VLOOKUP в Excel ищет значение, сопоставляя его с первым столбцом таблицы, и возвращает соответствующее значение из определенного столбца в той же строке.
Функция CHOOSE возвращает значение из списка аргументов значений на основе заданного индексного номера. Например, CHOOSE(3,”Apple”,”Peach”,”Orange”) возвращает Orange, так как индексный номер равен 3, а Orange — это третье значение после индексного номера в функции.
Связанные формулы
Поиск слева с помощью INDEX и MATCH
Чтобы найти информацию, указанную в левых столбцах электронной таблицы Excel, о заданном элементе, который находится справа, вы можете использовать функции INDEX и MATCH. Комбинация этих двух функций имеет преимущество поиска значений в любом столбце перед другой мощной функцией поиска Excel, VLOOKUP.
Поиск значений из другого листа или книги
Если вы знаете, как использовать функцию VLOOKUP для поиска значений на одном листе, поиск значений из другого листа или книги не составит для вас проблемы.
Поиск ближайшего совпадения с несколькими критериями
В некоторых случаях вам может потребоваться найти ближайшее или приблизительное совпадение на основе более чем одного критерия. С помощью комбинации функций INDEX, MATCH и IF вы сможете быстро выполнить эту задачу в Excel.
Лучшие инструменты для повышения производительности Office
Kutools для Excel - Помогает вам выделиться из толпы
🤖 | KUTOOLS AI Помощник: Революционизируйте анализ данных на основе: Интеллектуальное выполнение | Генерация кода | Создание пользовательских формул | Анализ данных и создание диаграмм | Вызов функций Kutools… |
Популярные функции: Поиск, Выделение или Отметка дубликатов | Удалить пустые строки | Объединить столбцы или ячейки без потери данных | Округлить без формулы ... | |
Супер VLookup: Множественные критерии | Множественные значения | На нескольких листах | Распознавание нечетких соответствий... | |
Расширенный раскрывающийся список: Простой раскрывающийся список | Зависимый раскрывающийся список | Раскрывающийся список с множественным выбором... | |
Управление столбцами: Добавить определенное количество столбцов | Переместить столбцы | Переключить статус видимости скрытых столбцов | Сравнить столбцы для выбора одинаковых и разных ячеек ... | |
Основные функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формул | Управление книгами и листами | Библиотека автотекста (Авто текст) | Выбор даты | Объединить данные | Шифрование/Расшифровка ячеек | Отправка писем по списку | Супер фильтр | Специальный фильтр (фильтр жирного/курсивного/зачеркнутого текста...) ... | |
Топ-15 наборов инструментов: 12 инструментов для работы с текстом (Добавить текст, Удалить определенные символы ...) | 50+ типов диаграмм (Диаграмма Ганта ...) | 40+ практических формул (Расчет возраста на основе даты рождения ...) | 19 инструментов вставки (Вставить QR-код, Вставить изображение по пути ...) | 12 инструментов преобразования (Преобразовать в слова, Конвертация валюты ...) | 7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки Excel ...) | ... и многое другое |
Kutools для Excel имеет более 300 функций, гарантируя, что то, что вам нужно, находится всего в одном клике...
Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)
- Один щелчок, чтобы переключаться между десятками открытых документов!
- Сократите сотни кликов мышью каждый день, попрощайтесь с болью в руке от мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.