Загальна формула
=VLOOKUP(val&"",table,col,0)
Резюме
Щоб використовувати функцію VLOOKUP для отримання інформації з таблиці, де значеннями ключів є числа, що зберігаються як текст, ви можете використовувати формулу, яка об’єднує порожній рядок ("") із числовим значенням пошуку, примушуючи його до тексту. У наведеному прикладі формула в H3 має вигляд:
=VLOOKUP(id&"",planets,2,0) // returns "Earth"
де id (H2) і планети (B3: B11) називаються діапазонами.
Примітка: Приклад - це вирішення проблеми невідповідності чисел та тексту, що спричиняє помилку # N / A. Якщо розбіжностей немає, обхідний шлях не потрібен, і ви можете використовувати звичайну формулу VLOOKUP.
Пояснення
Поширеною помилкою VLOOKUP є невідповідність цифр та тексту. Найчастіше стовпець підстановки в таблиці містить числові значення, які виглядають як числа, але насправді є числами, що зберігаються як текст. Коли справжнє число передається у VLOOKUP як перший аргумент, формула повертає помилку # N / A, хоча, схоже, і збіг. На екрані нижче наведено приклад цієї проблеми:
Числа у стовпці B насправді є текстом, тому числове значення пошуку 3 не вдається, хоча здається, що VLOOKUP повинен відповідати B5 і повернути "Землю". Ви можете ввести число як текстове значення, передуючи номеру однією лапкою (').
Найкраще рішення - переконатися, що значення пошуку в таблиці справді є цифрами. Однак, якщо у вас немає контролю над таблицею, ви можете змінити формулу VLOOKUP, щоб примусити значення пошуку відповідати типу в таблиці. У наведеному прикладі ми примушуємо числове значення пошуку до тексту, об'єднуючи порожній рядок:
=VLOOKUP(id,planets,2,0) // original =VLOOKUP(id&"",planets,2,0) // revised
І переглянута формула піклується про помилку:
Ви також можете зробити те ж саме з довшою формулою, яка використовує функцію TEXT для перетворення числа в текст:
=VLOOKUP(TEXT(id,"@"),planets,2,0)
І цифри, і текст
Якщо ви не можете бути впевнені, коли у вас будуть числа і коли у вас буде текст, ви можете задовольнити обидва варіанти, обернувши VLOOKUP у функцію IFERROR і використовуючи формулу, яка обробляє обидва випадки:
=IFERROR(VLOOKUP(id,planets,3,0),VLOOKUP(id&"",planets,3,0))
Тут ми спочатку спробуємо звичайну формулу VLOOKUP, яка приймає як значення пошуку, так і перший стовпець у таблицях - це цифри. Якщо це викликає помилку, ми спробуємо ще раз із оновленою формулою. Якщо ця формула також не вдається, VLOOKUP поверне помилку # N / A, як завжди.
