✅ Як зробити Excel VLOOKUP чутливим до регістру

Зміст

Надання VLOOKUP чутливості до регістру

За замовчуванням значення пошуку у функції VLOOKUP не враховує регістр. Наприклад, якщо ваше значення пошуку MATT, matt або Matt, для функції VLOOKUP все одно. Він поверне перше відповідне значення незалежно від випадку.

Надання VLOOKUP чутливості до регістру

Припустимо, у вас є дані, як показано нижче:

Як бачите, є три клітинки з однаковою назвою (A2, A4 та A5), але з різним регістром літер. Праворуч (у E2: F4) ми маємо три імена (Matt, MATT та matt) разом з їхніми оцінками в математиці.

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

У цьому посібнику ви дізнаєтесь, як зробити VLOOKUP чутливим до регістру:

Використання колонки помічника.
Без використання стовпця -помічника та використання формули.

Врахування регістру VLOOKUP чутливим - за допомогою стовпця помічника

Допоміжний стовпець можна використовувати для отримання унікального значення пошуку для кожного елемента в масиві пошуку. Це допомагає розрізняти імена з різними літерами.

Нижче наведено кроки для цього.

Вставте допоміжний стовпець ліворуч від стовпця, звідки ви хочете отримати дані. У наведеному нижче прикладі вам потрібно вставити допоміжний стовпець між стовпцями А та С.
У допоміжний стовпець введіть формулу = ROW (). Він вставить номер рядка в кожну клітинку.
Використовуйте таку формулу в комірці F2, щоб отримати результат пошуку з урахуванням регістру.
```
= VLOOKUP (МАКС. (ТОЧНО (E2, $ A $ 2: $ A $ 9)*(РЯД ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)
```
Скопіюйте, вставте його для інших клітинок (F3 та F4).

Примітка: Оскільки це формула масиву, використовуйте Control + Shift + Enter замість просто enter.

Як це працює?

Давайте розберемо формулу, щоб зрозуміти, як вона працює:

EXACT (E2, $ A $ 2: $ A $ 9) - Ця частина порівнює значення пошуку в E2 з усіма значеннями в A2: A9. Він повертає масив TRUEs/FALSEs, де TRUE повертається, якщо є точна відповідність. У цьому випадку, якщо значення в E2 є Метт, воно поверне такий масив:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
EXACT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9) - Ця частина множить масив ІСТИН/НЕВІРНОСТІ на номер рядка A2: A9. Де є ІСТИНА, це дає номер рядка, інакше він дає 0. У цьому випадку він повертає {2; 0; 0; 0; 0; 0; 0; 0}.
MAX (ТОЧНО (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))) - Ця частина повертає максимальне значення з масиву чисел. У цьому випадку він поверне 2 (це номер рядка, де є точна відповідність).
Тепер ми просто використовуємо це число як значення пошуку та використовуємо масив пошуку як B2: C9.

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

Тепер, якщо ви не прихильник допоміжного стовпця, ви також можете здійснити пошук з урахуванням регістру без стовпця-помічника.

Зробити VLOOKUP чутливим до регістру - без стовпця -помічника

Навіть якщо ви не хочете використовувати стовпець -помічник, вам все одно потрібно мати віртуальний стовпець -помічник. Цей віртуальний стовпець не є частиною аркуша, а побудований за формулою (як показано нижче).

Ось формула, яка дасть вам результат без стовпця -помічника:

= VLOOKUP (МАКС. (ТОЧНИЙ (D2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))), ВИБІРИ ({1,2}, ROW ($ A $ 2: $ A $ 9) , $ B $ 2: $ B $ 9), 2,0)

Як це працює?

У формулі також використовується поняття допоміжного стовпця. Відмінність полягає в тому, що замість того, щоб поміщати стовпець -помічник у аркуш, розгляньте його як дані віртуальних помічників, які є частиною формули.

Ось частина, яка працює як допоміжні дані (виділено помаранчевим кольором):

= VLOOKUP (МАКС. (ТОЧНО (D2, $ A $ 2: $ A $ 9)*(РЯД ($ A $ 2: $ A $ 9))),ВИБРАТИ ({1,2}, РЯД ($ A $ 2: $ A $ 9), $ B $ 2: $ B $ 9),2,0)

Дозвольте мені показати вам, що я маю на увазі під даними віртуальних помічників.

На наведеному вище малюнку, коли я вибираю частину формули ВИБІР і натискаю F9, вона показує результат, який дасть формула ВИБІР.

Результат: {2,38; 3,88; 4,57; 5,82; 6,55; 7,44; 8,75; 9,38}

Це масив, де кома представляє наступну клітинку в тому ж рядку, а крапка з комою означає, що наступні дані знаходяться в наступному рядку. Отже, ця формула створює 2 стовпці даних - в одному стовпці є номер рядка, а в одному - математичний бал.

Тепер, коли ви використовуєте функцію VLOOKUP, вона просто шукає значення пошуку в першому стовпці (цих даних віртуального 2 стовпця) і повертає відповідну оцінку. Значення пошуку тут - це число, яке ми отримуємо від поєднання функцій MAX і EXACT.

Завантажте файл прикладу