Пошук даних у Excel 2010 за допомогою HLOOKUP та VLOOKUP

Найпопулярнішими функціями пошуку в Excel 2010 є HLOOKUP (для горизонтального пошуку) і VLOOKUP (для вертикального пошуку). Ці функції розташовані в категорії «Пошук і посилання» на вкладці «Формули» на стрічці та в діалоговому вікні «Вставка функції». Вони є частиною потужної групи функцій, які можуть повертати значення, шукаючи їх у таблицях даних.

Функція HLOOKUP виконує горизонтальний пошук (зліва направо) у найвищому рядку таблиці пошуку, доки не знайде значення, яке збігається або майже відповідає тому, яке ви шукаєте. Функція VLOOKUP виконує вертикальний пошук (зверху вниз) у крайньому лівому стовпці таблиці пошуку, доки не знайде значення, яке збігається або майже відповідає тому, яке ви шукаєте.

При використанні функцій VLOOKUP і HLOOKUP текстові або числові записи в стовпці або рядку пошуку (тобто крайній лівий стовпець вертикальної таблиці пошуку або верхній рядок горизонтальної таблиці пошуку) повинні бути унікальними. Ці записи також мають бути впорядковані або відсортовані в порядку зростання; тобто алфавітний порядок для текстових записів і порядок від найнижчого до найвищого для цифрових записів.

Функція HLOOKUP використовує такий синтаксис:

=HLOOKUP(значення_шукання,масив_таблиці,число_індексу_рядка,[діапазон_пошуку])

Функція VLOOKUP дотримується майже ідентичного синтаксису:

=VLOOKUP(значення_шукання,масив_таблиці,число_індексу_стовпця,[діапазон_пошуку])

В обох функціях аргумент lookup_value — це значення, яке ви хочете знайти в таблиці, а table_array — це діапазон комірок або ім’я таблиці, що містить як значення для пошуку, так і пов’язане значення для повернення. Аргумент row_index_num у функції HLOOKUP є номером рядка, значення якого потрібно повернути; номер_стовпчика аргумент у функції ВПР є номер стовпця, значення якого ви хочете повернути.

Необов’язковий аргумент range_lookup як у функціях VLOOKUP, так і у функціях HLOOKUP є логічним значенням TRUE або FALSE, який вказує, чи потрібно Excel знайти точну чи приблизну відповідність для lookup_value в table_array . Коли ви вказуєте TRUE або пропускаєте аргумент range_lookup , Excel знаходить приблизну відповідність. Коли ви вказуєте FALSE як аргумент range_lookup , Excel знаходить лише точні збіги.

Знаходження приблизних збігів відноситься лише до того, коли ви шукаєте числові записи (а не текст). Коли Excel не знаходить точну відповідність в цьому стовпці підстановок або рядках, він знаходить наступне по величині значення , яке не перевищує Lookup_Value аргументу і повертає значення в стовпці або рядку позначеного Col_Index_Num або номер_рядка аргументів.

На малюнку нижче показано приклад використання функції VLOOKUP для повернення 15% або 20% підказки з таблиці підказок. У клітинці F3 міститься функція VLOOKUP:

=VLOOKUP(Усього перед оподаткуванням,Таблиця_підказки,IF(Відсоток_підказки=0,15,2,3))

Ця формула повертає суму чайових на основі відсотка чайових у клітинці F1 і суми чека до оподаткування в клітинці F2.

Використання функції VLOOKUP для повернення суми чайових для додавання з таблиці пошуку.

Щоб використовувати цю таблицю чайових, введіть відсоток чайових (15% або 20%) у клітинку F1 (з назвою Tip_Percentage) і суму чека до податку в клітинку F2 (під назвою Pretax_Total). Потім Excel шукає значення, введене в клітинку Pretax_Total у таблиці пошуку, яка включає діапазон комірок A2:C101 і називається Tip_Table.

Потім Excel переміщує значення в першому стовпці Tip_Table вниз, доки не знайде відповідність. Якщо Excel виявить, що значення, введене в клітинку Pretax_Total (16,50 доларів США в цьому прикладі), не точно відповідає одному зі значень у першому стовпці Tip_Table, програма продовжує пошук у діапазоні порівняння, доки не зустріне перше значення, яке перевищує загальна сума до оподаткування (17,00 у клітинці A19 у цьому прикладі). Потім Excel повертається до попереднього рядка таблиці й повертає значення в стовпці, яке відповідає аргументу col_index_num функції VLOOKUP (це тому, що необов’язковий аргумент range_lookup було пропущено у функції).

Зверніть увагу, що аргумент col_index_num використовує оператор IF, щоб визначити значення стовпця, яке повернути. У цьому випадку, якщо значення Tip_Percentage дорівнює 0,15, то функція повертає значення у другому стовпці таблиці . В іншому випадку він повертає значення в третьому стовпці того ж рядка.