Генеричка формула
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Резиме
Да бисте поништили ВЛООКУП - тј. Да бисте пронашли оригиналну вредност претраживања помоћу резултата формуле ВЛООКУП - можете да користите шкакљиву формулу засновану на функцији ИЗБОР или једноставније формуле засноване на ИНДЕКС-у и МАТЦХ-у или КСЛООКУП-у како је објашњено у наставку. У приказаном примеру, формула у Х10 је:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Овим подешавањем, ВЛООКУП проналази опцију повезану са трошком од 3000 и враћа „Ц“.
Напомена: ово је напреднија тема. Ако тек започињете са ВЛООКУП-ом, почните овде.
Увод
Кључно ограничење ВЛООКУП-а је то што може тражити само вредности удесно. Другим речима, колона са вредностима претраживања мора бити лево од вредности које желите да дохватите помоћу ВЛООКУП-а. Као резултат, са стандардном конфигурацијом, не постоји начин да се помоћу ВЛООКУП-а „погледа лево“ и преокрене оригинални преглед.
Са становишта ВЛООКУП-а, проблем можемо визуализовати овако:
Заобилазно решење објашњено у наставку користи функцију ЦХООСЕ за преуређивање табеле унутар ВЛООКУП-а.
Објашњење
Почевши од почетка, формула у Х5 је нормална ВЛООКУП формула:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Користећи Г5 као вредност претраживања ("Ц") и податке у Б5: Д8 као низ табеле, ВЛООКУП врши преглед вредности у колони Б и враћа одговарајућу вредност из колоне 3 (колона Д), 3000. Обавештење нула (0) је наведена као последњи аргумент за форсирање тачног подударања.
Формула у Г10 једноставно повлачи резултат из Х5:
=H5 // 3000
Да би се извршило обрнуто претраживање, формула у Х10 је:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Лукав бит је функција ЦХООСЕ, која се користи за преуређивање низа табеле тако да је Цост прва колона, а Оптион последња:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Функција ЦХООСЕ је дизајнирана за одабир вредности на основу нумеричког индекса. У овом случају испоручујемо три вредности индекса у константи низа:
(3,2,1) // array constant
Другим речима, тражимо колону 3, затим колону 2, па колону 1. Након тога следе три опсега који представљају сваку колону табеле редоследом којим се појављују на радном листу.
Са овом конфигурацијом, ЦХООСЕ враћа све три колоне у једном 2Д низу попут овог:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Ако овај низ визуализујемо као табелу на радном листу, имамо:
Напомена: наслови нису део низа и овде су приказани само ради прегледности.
У ствари, заменили смо колоне 1 и 3. Реорганизована табела се враћа директно у ВЛООКУП, који се подудара са 3000, и враћа одговарајућу вредност из колоне 3, "Ц".
Уз ИНДЕКС и УТАКМИЦУ
Горње решење добро функционише, али је тешко препоручити га јер већина корисника неће разумети како формула делује. Боље решење су ИНДЕКС и МАТЦХ, користећи формулу попут ове:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Овде функција МАТЦХ проналази вредност 3000 у Д5: Д8 и враћа свој положај 3:
MATCH(G10,D5:D8,0) // returns 3
Напомена: МАТЦХ је конфигурисан за тачно подударање постављањем последњег аргумента на нулу (0).
МАТЦХ враћа резултат директно у ИНДЕКС као број реда, па формула постаје:
=INDEX(B5:B8,3) // returns "C"
и ИНДЕКС враћа вредност из трећег реда Б5: Б8, "Ц".
Ова формула показује како ИНДЕКС и МАТЦХ могу бити флексибилнији од ВЛООКУП-а.
Уз КСЛООКУП
КСЛООКУП такође нуди врло добро решење. Еквивалентна формула је:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Са траженом вредношћу из Г10 (3000), свим претраживачким низом Д5: Д8 (трошкови) и низом резултата Б5: Б8 (опције), КСЛООКУП лоцира 3000 у низу претраживања и враћа одговарајућу ставку из низа резултата, "Ц". Будући да КСЛООКУП подразумевано изводи тачно подударање, нема потребе да експлицитно подесите начин подударања.
