Обща формула
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Обобщение
За да обърнете VLOOKUP - т.е. да намерите първоначалната стойност за търсене, използвайки резултат от формула VLOOKUP - можете да използвате хитра формула, базирана на функцията CHOOSE, или по-ясни формули, базирани на INDEX и MATCH или XLOOKUP, както е обяснено по-долу. В показания пример формулата в H10 е:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
С тази настройка VLOOKUP намира опцията, свързана с цена от 3000, и връща "C".
Забележка: това е по-напреднала тема. Ако тепърва започвате с VLOOKUP, започнете от тук.
Въведение
Основно ограничение на VLOOKUP е, че може да търси само стойности вдясно. С други думи, колоната със справочни стойности трябва да е вляво от стойностите, които искате да извлечете с VLOOKUP. В резултат на това при стандартната конфигурация няма начин да се използва VLOOKUP, за да се "гледа наляво" и да се обърне оригиналното търсене.
От гледна точка на VLOOKUP можем да визуализираме проблема по следния начин:
Обясненото по-долу решение използва функцията CHOOSE за пренареждане на таблицата във VLOOKUP.
Обяснение
Започвайки от началото, формулата в H5 е нормална формула VLOOKUP:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Използвайки G5 като справочна стойност ("C") и данните в B5: D8 като масив на таблицата, VLOOKUP извършва търсене на стойности в колона B и връща съответната стойност от колона 3 (колона D), 3000. Забележка нула (0) е предоставена като последен аргумент за налагане на точно съвпадение.
Формулата в G10 просто извлича резултата от H5:
=H5 // 3000
За да извършите обратно търсене, формулата в H10 е:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Трудният бит е функцията CHOOSE, която се използва за пренареждане на масива на таблицата, така че Cost да е първата колона, а Option - последната:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Функцията CHOOSE е предназначена за избор на стойност въз основа на числов индекс. В този случай ние предоставяме три стойности на индекса в константа на масива:
(3,2,1) // array constant
С други думи, ние искаме колона 3, след това колона 2, след това колона 1. Това е последвано от трите диапазона, които представляват всяка колона от таблицата в реда, в който се появяват на работния лист.
С тази конфигурация CHOOSE връща и трите колони в един двуизмерен масив по следния начин:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Ако визуализираме този масив като таблица на работния лист, имаме:
Забележка: заглавията не са част от масива и са показани тук само за по-голяма яснота.
На практика сме разменяли колони 1 и 3. Реорганизираната таблица се връща директно към VLOOKUP, който съответства на 3000, и връща съответната стойност от колона 3, "C".
С ИНДЕКС и МАТЧ
Горното решение работи добре, но е трудно да се препоръча, тъй като повечето потребители няма да разберат как работи формулата. По-добро решение са INDEX и MATCH, като се използва формула като тази:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Тук функцията MATCH намира стойността 3000 в D5: D8 и връща позицията си 3:
MATCH(G10,D5:D8,0) // returns 3
Забележка: MATCH е конфигуриран за точно съвпадение чрез задаване на последния аргумент на нула (0).
MATCH връща резултат директно в INDEX като номер на реда, така че формулата става:
=INDEX(B5:B8,3) // returns "C"
и INDEX връща стойността от третия ред на B5: B8, "C".
Тази формула показва как INDEX и MATCH могат да бъдат по-гъвкави от VLOOKUP.
С XLOOKUP
XLOOKUP също предлага много добро решение. Еквивалентната формула е:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Със справочна стойност от G10 (3000), всички масиви на търсене от D5: D8 (разходи) и масив с резултати от B5: B8 (опции), XLOOKUP локализира 3000 в масива за търсене и връща съответния елемент от масива с резултати, "° С". Тъй като XLOOKUP изпълнява точно съвпадение по подразбиране, не е необходимо да задавате изрично режима на съвпадение.
