Обща формула
=VLOOKUP(A1,CHOOSE((1,2),range2,range1),2,0)
Обобщение
За да използвате VLOOKUP за извършване на търсене вляво, можете да използвате функцията CHOOSE за пренареждане на таблицата за търсене. В показания пример формулата във F5 е:
=VLOOKUP(E5,CHOOSE((1,2),score,rating),2,0)
където резултатът (C5: C9) и рейтингът (B5: B9) са наречени диапазони.
Обяснение
Едно от основните ограничения на функцията VLOOKUP е, че тя може да търси само стойности вдясно. С други думи, колоната, която съдържа справочни стойности, трябва да се намира вляво от стойностите, които искате да извлечете с VLOOKUP. Няма начин да се отмени това поведение, тъй като то е включено във функцията. В резултат на това при нормална конфигурация няма начин да се използва VLOOKUP за търсене на рейтинг в колона Б въз основа на резултат в колона В.
Едно решение е да преструктурирате самата справочна таблица и да преместите търсещата колона вляво от справочната стойност (и). Това е подходът, взет в този пример, който използва функцията CHOOSE обратна оценка и резултат по този начин:
CHOOSE((1,2),score,rating)
Обикновено CHOOSE се използва с единичен номер на индекс като първи аргумент, а останалите аргументи са стойностите, от които можете да избирате. Тук обаче даваме константа на масив за индексно число, съдържащо две числа: (1,2). По същество искаме да изберем както първата, така и втората стойност.
Стойностите се предоставят като двата имена в обхвата в примера: оценка и оценка. Забележете обаче, че предоставяме тези диапазони в обратен ред. Функцията CHOOSE избира двата диапазона в предоставения ред и връща резултата като единичен масив като този:
(5,"Excellent";4,"Good";3,"Average";2,"Poor";1,"Terrible")
CHOOSE връща този масив директно към VLOOKUP като аргумент на масива на таблицата. С други думи, CHOOSE доставя справочна таблица като тази на VLOOKUP:
Използвайки справочната стойност в E5, VLOOKUP локализира съвпадение в новосъздадената таблица и връща резултат от втората колона.
Пренареждане с константата на масива
В показания пример ние пренареждаме таблицата за търсене, като обръщаме "рейтинг" и "резултат" вътре в избраната функция. Вместо това обаче бихме могли да използваме константата на масива, за да пренаредим така:
CHOOSE((2,1),rating,score)
Резултатът е абсолютно същият.
С ИНДЕКС и МАТЧ
Въпреки че горният пример работи добре, той не е идеален. Първо, повечето обикновени потребители няма да разберат как работи формулата. По-естествено решение е ИНДЕКС и МАТЧ. Ето еквивалентната формула:
=INDEX(rating,MATCH(E5,score,0))
Всъщност това е добър пример за това как INDEX и MATCH са по-гъвкави от VLOOKUP.
