Пошук значень за допомогою функцій VLOOKUP, INDEX або MATCH

Microsoft 365 допомагає працювати ефективніше

Придбайте передплату зараз

Порада.: Скористайтеся новими функціями Xlookup і xlookup , удосконалені версії функцій, описаних у цій статті. Ці нові функції працюють в будь-якому напрямі та повертають точні відповідники за замовчуванням, завдяки чому їх легше і зручніше використовувати, ніж їхні попередники.

Припустимо, що у вас є список номерів розташування Office, і вам потрібно знати, які працівники мають в кожній програмі Office. Електронна таблиця – величезна, тому ви можете подумати, що це складна задача. Це насправді дуже легко зробити за допомогою функції LOOKUP.

Функції VLOOKUP та HLOOKUP разом з індексами та відповіднимифункціями є одними з найкорисніших функцій у програмі Excel.

Примітка.: Функція майстра підстановок більше не доступна в програмі Excel.

Нижче наведено приклад використання функції VLOOKUP.

=VLOOKUP(B2;C2:E7;3;TRUE)

У цьому прикладі B2 – це перший аргумент– елемент даних, для яких має працювати функція. Для функції VLOOKUP цей перший аргумент – це значення, яке потрібно відшукати. Цей аргумент може бути посиланням на клітинку або фіксованим значенням, як-от "Коваль" або 21 000. Другий аргумент – це діапазон клітинок, C2-: E7, у якому потрібно шукати значення, яке потрібно знайти. Третій аргумент – це стовпець у діапазоні клітинок із потрібним значенням.

Четвертий аргумент необов’язковий. Укажіть значення TRUE або FALSE. Якщо ввести TRUE або залишити аргумент пустим, функція поверне приблизний збіг зі значенням, указаним у першому аргументі. Якщо ввести значення FALSE, функція буде відповідати значенню, яке надається першим аргументом. Іншими словами, залишаючи четвертий аргумент пустий або повертає значення TRUE, дає змогу збільшити гнучкість.

У цьому прикладі показано, як працює функція. Коли ви введете значення у клітинці B2 (перший аргумент), ФУНКЦІЯ VLOOKUP шукає клітинки в діапазоні C2: E7 (2-й аргумент) і повертає найближчий приблизний збіг від третього стовпця в діапазоні, стовпець E (третій аргумент).

Типове використання функції VLOOKUP

Четвертий аргумент має значення "Пусто", тому функція повертає приблизну відповідність. Якщо не повертає, потрібно ввести одне зі значень у стовпці C або D, щоб отримати хоч якийсь результат.

Якщо ви зручно використовувати функцію VLOOKUP, функція HLOOKUP однаково проста у використанні. Ви ввели ті самі аргументи, але він шукає у рядках замість стовпців.

Використання ІНДЕКСУ та відповідності замість функції VLOOKUP

За допомогою функції VLOOKUP існують певні обмеження: функція VLOOKUP може шукати лише значення зліва направо. Це означає, що стовпець, що містить значення, яке потрібно знайти, має завжди бути розташовано ліворуч від стовпця, який містить повернуте значення. Тепер, якщо електронна таблиця не вбудовано таким чином, не використовуйте ФУНКЦІЯ VLOOKUP. Натомість використовуйте сполучення функцій INDEX і MATCH.

У цьому прикладі відображається невеликий список, у якому значення, яке потрібно знайти в Чикаго, розташовано не в крайньому лівому стовпці. Таким чином, ми не можемо використовувати ФУНКЦІЯ VLOOKUP. Натомість ми використовуватимемо функцію MATCH, щоб знайти Чикаго в діапазоні B1: B11. Файл знайдено в рядку 4. Потім індекс використовує це значення як аргумент підстановки та знаходить населення для Чикаго в 4-му стовпці (стовпець D). Формула, що використовується, відображається в полі "стільниковий A14".

Використання функцій INDEX і MATCH для пошуку значення

Додаткові приклади використання ІНДЕКСУ та відповідності замість функції VLOOKUP наведено в статті https://www.mrexcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ by Bill Jelen, Microsoft MVP.

Спробуйте

Якщо ви хочете поекспериментувати з функціями підстановки, перш ніж їх використовувати за допомогою власних даних, ось кілька прикладів даних.

Приклад функції VLOOKUP на роботі

Скопіюйте наведені нижче дані до пустої електронної таблиці.

Порада.: Перш ніж вставляти дані в Excel, установіть ширину стовпців для стовпців, а потім – C до 250 пікселів, а потім натисніть кнопку обтікання текстом (вкладкаосновне , Група вирівнювання ).

Густина

В'язкість

Температура

0,457

3,55

500

0,525

3,25

400

0,606

2,93

300

0,675

2,75

250

0,746

2,57

200

0,835

2,38

150

0,946

2,17

100

1,09

1,95

50

1,29

1,71

0

Формула

Опис

Результат

=VLOOKUP(1,A2:C10,2)

Використовуючи приблизний збіг, шукає значення 1 у стовпці A, знаходить найбільше значення, менше або рівне 1, у стовпці A (яке дорівнює 0,946), а потім повертає значення зі стовпця B того самого рядка.

2,17

=VLOOKUP(1;A2:C10;3;ІСТИНА)

Використовуючи приблизний збіг, шукає значення 1 у стовпці A, знаходить найбільше значення, менше або рівне 1, у стовпці A (яке дорівнює 0,946), а потім повертає значення зі стовпця С того самого рядка.

100

=VLOOKUP(0,7;A2:C10;3;ХИБНІСТЬ)

Використовуючи точний збіг, шукає значення 0,7 у стовпці A. Оскільки у стовпці А відсутній точний збіг, результат повертає помилку.

#N/A

=VLOOKUP(0.1,A2:C10,2,ІСТИНА)

Використовуючи приблизний збіг, шукає значення 0,1 у стовпці A. Оскільки 0,1 менше за найменше значення у стовпці А, результат повертає помилку.

#N/A

=VLOOKUP(2,A2:C10,2,ІСТИНА)

Використовуючи приблизний збіг, шукає значення 2 у стовпці A, знаходить найбільше значення, менше або рівне 2, у стовпці A (яке дорівнює 1,29), а потім повертає значення зі стовпця B того самого рядка.

1,71

Приклад для HLOOKUP

Скопіюйте всі клітинки в цій таблиці та вставте їх у клітинку A1 на пустому аркуші Excel.

Порада.: Перш ніж вставляти дані в Excel, установіть ширину стовпців для стовпців, а потім – C до 250 пікселів, а потім натисніть кнопку обтікання текстом (вкладкаосновне , Група вирівнювання ).

Осі

Опори

Гвинти

4

4

9

5

7

10

6

8

11

Формула

Опис

Результат

=HLOOKUP("Осі";A1:C4;2;TRUE)

Шукає слово «Осі» в рядку 1 і повертає значення з рядка 2, розташованого в тому самому стовпці (стовпець A).

4

=HLOOKUP("Опори";A1:C4;3;FALSE)

Шукає слово «Опори» в рядку 1 і повертає значення з рядка 3, розташованого в тому самому стовпці (стовпець B).

7

=HLOOKUP("B";A1:C4;3;TRUE)

Шукає літеру «В» в рядку 1 і повертає значення з рядка 3, розташованого в тому самому стовпці. Оскільки точний збіг для літери «В» не знайдено, використовується найбільше значення в рядку 1, яке менше «В»: «Осі» у стовпці A.

5

=HLOOKUP("Гвинти";A1:C4;4)

Шукає слово «Гвинти» в рядку 1 і повертає значення з рядка 4, розташованого в тому самому стовпці (стовпець С).

11

=HLOOKUP(3;{1,2,3;"a","b","c";"d","e","f"};2;TRUE)

Шукає число 3 в константі-масиві із трьох рядків і повертає значення з рядка 2, розташованого в тому самому стовпці (у цьому випадку, третьому). Константа масиву містить три рядки значень, кожен із рядків відокремлено крапкою з комою (;). Оскільки символ «с» знайдено в рядку 2 та в тому самому стовпці що й 3, символ «c» повертається.

c

Приклади ІНДЕКСУ та відповідності

У цьому останньому прикладі використовуються функції INDEX і MATCH разом, щоб повернути найраніший номер рахунка та відповідну дату для кожного з п'яти міст. Оскільки дата повертається як число, функція TEXT використовується для форматування як дати. Функція INDEX як аргумент фактично використовує результат функції MATCH. Поєднання функцій INDEX і MATCH використовується в кожній формулі двічі: перший раз для повернення номера рахунка, другий – для повернення дати.

Скопіюйте всі клітинки в цій таблиці та вставте їх у клітинку A1 на пустому аркуші Excel.

Порада.: Перш ніж вставляти дані в програму Excel, установіть ширину стовпців для стовпців A-D до 250 пікселів, а потім натисніть кнопку обтікання текстом (вкладкаосновне , Група вирівнювання ).

Рахунок

Місто

Дата виставлення рахунка

Перший рахунок за містом із датою

3115

Атланта

7/4/12

="Чернігів = "&INDEX($A$2:$C$33,MATCH("Чернігів",$B$2:$B$33,0),1)& ", Дата виписки рахунка: " & TEXT(INDEX($A$2:$C$33,MATCH("Чернігів",$B$2:$B$33,0),3),"m/d/yy")

3137

Атланта

9/4/12

="Алушта = "&INDEX($A$2:$C$33,MATCH("Алушта",$B$2:$B$33,0),1)& ", Дата виписки рахунка: " & TEXT(INDEX($A$2:$C$33,MATCH("Алушта",$B$2:$B$33,0),3),"m/d/yy")

3154

Атланта

11/4/12

="Донецьк = "&INDEX($A$2:$C$33,MATCH("Донецьк",$B$2:$B$33,0),1)& ", Дата виписки рахунка: " & TEXT(INDEX($A$2:$C$33,MATCH("Донецьк",$B$2:$B$33,0),3),"m/d/yy")

3191

Атланта

21/4/12

="Нововолинськ = "&INDEX($A$2:$C$33,MATCH("Нововолинськ",$B$2:$B$33,0),1)& ", Дата виписки рахунка: " & TEXT(INDEX($A$2:$C$33,MATCH("Нововолинськ",$B$2:$B$33,0),3),"m/d/yy")

3293

Атланта

25/4/12

="Суми = "&INDEX($A$2:$C$33,MATCH("Суми",$B$2:$B$33,0),1)& ", Дата виписки рахунка: " & TEXT(INDEX($A$2:$C$33,MATCH("Суми",$B$2:$B$33,0),3),"m/d/yy")

3331

Атланта

27/4/12

3350

Атланта

28/4/12

3390

Атланта

1/5/12

3441

Атланта

2/5/12

3517

Чернігів

8/5/12

3124

Алушта

9/4/12

3155

Алушта

11/4/12

3177

Алушта

19/4/12

3357

Алушта

28/4/12

3492

Алушта

6/5/12

3316

Донецьк

25/4/12

3346

Донецьк

28/4/12

3372

Донецьк

1/5/12

3414

Донецьк

1/5/12

3451

Донецьк

2/5/12

3467

Донецьк

2/5/12

3474

Донецьк

4/5/12

3490

Донецьк

5/5/12

3503

Донецьк

8/5/12

3151

Нововолинськ

9/4/12

3438

Нововолинськ

2/5/12

3471

Нововолинськ

4/5/12

3160

Суми

18/4/12

3328

Суми

26/4/12

3368

Суми

29/4/12

3420

Суми

1/5/12

3501

Суми

6/5/12

Див. також

Короткий довідник:

і довідкові функції (довідка)

використання аргументу "TABLE_ARRAY" в функції VLOOKUP

Потрібна додаткова довідка?

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо, що знайшли час і надіслали нам відгук! Можливо, у нас не буде часу відповісти на кожен коментар, але докладемо максимум зусиль, щоб переглянути їх усі. Вас цікавить, як ми використовуємо ваші відгуки?

×