Generička formula
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Sažetak
Da biste poništili VLOOKUP - tj. Kako biste pronašli izvornu vrijednost pretraživanja pomoću rezultata formule VLOOKUP - možete upotrijebiti nezgodnu formulu koja se temelji na funkciji CHOOSE ili jednostavnije formule na temelju INDEX-a i MATCH-a ili XLOOKUP-a kako je objašnjeno u nastavku. U prikazanom primjeru formula u H10 je:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Ovom postavkom VLOOKUP pronalazi opciju povezanu s cijenom od 3000 i vraća "C".
Napomena: ovo je naprednija tema. Ako tek započinjete s VLOOKUP-om, počnite ovdje.
Uvod
Ključno ograničenje VLOOKUP-a je da može tražiti samo vrijednosti udesno. Drugim riječima, stupac s vrijednostima pretraživanja mora biti lijevo od vrijednosti koje želite dohvatiti pomoću VLOOKUP-a. Kao rezultat toga, sa standardnom konfiguracijom ne postoji način da se VLOOKUP koristi za "gledanje ulijevo" i obrtanje izvornog pretraživanja.
Sa stajališta VLOOKUP-a problem možemo vizualizirati ovako:
Dolje objašnjeno zaobilazno rješenje koristi funkciju CHOOSE za preuređivanje tablice unutar VLOOKUP-a.
Obrazloženje
Počevši od početka, formula u H5 normalna je VLOOKUP formula:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Koristeći G5 kao vrijednost pretraživanja ("C") i podatke u B5: D8 kao niz tablice, VLOOKUP vrši pretraživanje vrijednosti u stupcu B i vraća odgovarajuću vrijednost iz stupca 3 (stupac D), 3000. Obavijest nula (0) je navedena kao posljednji argument za forsiranje točnog podudaranja.
Formula u G10 jednostavno povlači rezultat iz H5:
=H5 // 3000
Za izvođenje obrnutog pretraživanja, formula u H10 je:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Lukav je bit funkcija CHOOSE, koja se koristi za preuređivanje niza tablice tako da je Cost prvi stupac, a Option zadnji:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Funkcija CHOOSE dizajnirana je za odabir vrijednosti na temelju numeričkog indeksa. U ovom slučaju isporučujemo tri vrijednosti indeksa u konstanti niza:
(3,2,1) // array constant
Drugim riječima, tražimo stupac 3, zatim stupac 2, pa stupac 1. Nakon toga slijede tri raspona koja predstavljaju svaki stupac tablice redoslijedom kako se pojavljuju na radnom listu.
S ovom konfiguracijom, CHOOSE vraća sva tri stupca u jedan 2D niz poput ovog:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Ako ovaj niz vizualiziramo kao tablicu na radnom listu, imamo:
Napomena: naslovi nisu dio polja i ovdje su prikazani samo radi jasnosti.
U stvari, zamijenili smo stupce 1 i 3. Reorganizirana tablica vraća se izravno u VLOOKUP koji odgovara 3000 i vraća odgovarajuću vrijednost iz stupca 3, "C".
Uz INDEKS i UTAKMICU
Gornje rješenje djeluje u redu, ali teško ga je preporučiti jer većina korisnika neće razumjeti kako formula funkcionira. Bolje rješenje su INDEX i MATCH, koristeći formulu poput ove:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Ovdje funkcija MATCH pronalazi vrijednost 3000 u D5: D8 i vraća svoj položaj 3:
MATCH(G10,D5:D8,0) // returns 3
Napomena: MATCH je konfiguriran za točno podudaranje postavljanjem zadnjeg argumenta na nulu (0).
MATCH vraća rezultat izravno u INDEX kao broj retka, pa formula postaje:
=INDEX(B5:B8,3) // returns "C"
a INDEX vraća vrijednost iz trećeg retka B5: B8, "C".
Ova formula pokazuje kako INDEX i MATCH mogu biti fleksibilniji od VLOOKUP-a.
Uz XLOOKUP
XLOOKUP također nudi vrlo dobro rješenje. Ekvivalentna formula je:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
S vrijednošću pretraživanja iz G10 (3000), svim pretraživačkim nizom D5: D8 (troškovi) i nizom rezultata B5: B8 (opcije), XLOOKUP pronalazi 3000 u polju pretraživanja i vraća odgovarajuću stavku iz niza rezultata, "C". Budući da XLOOKUP prema zadanim postavkama izvodi točno podudaranje, nije potrebno eksplicitno postaviti način podudaranja.
