Завантажте зразок робочої книги
У цьому посібнику буде показано, як виконати VLOOKUP з урахуванням регістру в Excel за допомогою двох різних методів та Google Таблиць за допомогою одного методу.
VLOOKUP з урахуванням регістру з колонкою помічника
Функція VLOOKUP
Функція VLOOKUP використовується для пошуку приблизного або точного відповідності значення в крайньому лівому стовпці діапазону і повертає відповідне значення з іншого стовпця. За замовчуванням VLOOKUP працюватиме лише для значень, які не враховують регістр, наприклад:
| 1 | = VLOOKUP ($ E $ 2, $ B $ 2: $ C $ 4,2,0) |
VLOOKUP з урахуванням регістру
Комбінуючи VLOOKUP, EXACT, MAX та ROW, ми можемо створити формулу VLOOKUP з урахуванням регістру, яка повертає відповідне значення для нашого VLOOKUP з урахуванням регістру. Пройдемося по прикладу.
У нас є список товарів та їх відповідні ціни (зверніть увагу, що ідентифікатор товару унікальний з урахуванням регістру):
Припустимо, нас попросять отримати ціну за товар, використовуючи його ідентифікатор товару таким чином:
Для цього нам у першу чергу потрібно створити допоміжний стовпець за допомогою ROW:
| 1 | = ROW () |
= Клацніть ROW () і перетягніть (або двічі клацніть), щоб попередньо заповнити всі рядки в діапазоні
Далі об’єднайте VLOOKUP, MAX, EXACT та ROW у формулу так:
| 12 | = VLOOKUP (MAX (ТОЧНИЙ (,)*(ROW ())),,, 0) |
| 1 | = VLOOKUP (МАКС. (ТОЧНО (E2, $ B $ 2: $ B $ 7)*(РЯД ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0) |
Як працює формула?
- Функція EXACT перевіряє ідентифікатор елемента в E2 (значення пошуку) щодо значень у B2: B7 (діапазон пошуку) і повертає масив TRUE, де є точна відповідність або FLASE в масиві {FLASE, FLASE, FLASE, FLASE, FLASE, TRUE}.
- Потім цей масив помножується на масив ROW {2, 3, 4, 5, 6, 7} (зверніть увагу, що це відповідає нашому стовпцю -помічнику).
- Функція MAX повертає максимальне значення з отриманого масиву {0,0,0,0,0,7}, що у нашому прикладі становить 7.
- Потім ми використовуємо результат як наше значення пошуку у VLOOKUP і вибираємо наш допоміжний стовпець як діапазон пошуку. У нашому прикладі формула повертає відповідне значення 16,00 доларів США.
Спосіб 2 - VLOOKUP з урахуванням регістру з "віртуальним" допоміжним стовпцем
Цей метод використовує ту саму логіку, що і перший метод, але усуває необхідність створення допоміжного стовпця і замість цього використовує CHOOSE і ROW для створення “віртуального” стовпця -помічника так:
| 12 | = VLOOKUP (MAX (ТОЧНИЙ (,)*(ROW ())),ВИБІРИ ({1,2}, ROW (),),, 0) |
| 1 | = VLOOKUP (МАКС. (ТОЧНИЙ (D2, $ B $ 2: $ B $ 7)*(РЯД ($ B $ 2: $ B $ 7))), ВИБІР ({1,2}, ROW ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0) |
Як працює формула?
- Перша частина формули працює так само, як і перший метод.
- Поєднання CHOOSE і ROW повертає масив з двома стовпцями, один для номера рядка, а інший для ціни. Масив розділяється крапкою з комою для представлення наступного рядка та коми для наступного стовпця так: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
- Потім ми можемо використовувати результат з першої частини формули у VLOOKUP, щоб знайти відповідне значення з нашого масиву CHOOSE і ROW.
VLOOKUP з урахуванням регістру в Таблицях Google
Щоб виконати VLOOKUP з урахуванням регістру в Google Таблицях, скористайтеся таким методом:
