Splošna formula
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Povzetek
Če želite razveljaviti VLOOKUP - tj. Najti prvotno vrednost iskanja z rezultatom formule VLOOKUP - lahko uporabite zapleteno formulo, ki temelji na funkciji IZBOR, ali bolj enostavne formule, ki temeljijo na INDEX in MATCH ali XLOOKUP, kot je razloženo spodaj. V prikazanem primeru je formula v H10:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
S to nastavitvijo VLOOKUP najde možnost, povezano s stroški 3000, in vrne "C".
Opomba: to je bolj napredna tema. Če šele začenjate z VLOOKUP-om, začnite tukaj.
Uvod
Ključna omejitev VLOOKUP-a je, da lahko išče vrednosti le desno. Z drugimi besedami, stolpec z iskalnimi vrednostmi mora biti levo od vrednosti, ki jih želite pridobiti z VLOOKUP. Kot rezultat, s standardno konfiguracijo ni mogoče uporabiti VLOOKUP za "pogled levo" in obrnitev prvotnega iskanja.
Z vidika VLOOKUP-a si lahko težavo predstavimo tako:
Spodaj razložena rešitev uporablja funkcijo CHOOSE za preureditev tabele znotraj VLOOKUP.
Pojasnilo
Od začetka je formula v H5 običajna formula VLOOKUP:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Z uporabo G5 kot iskalne vrednosti ("C") in podatkov v B5: D8 kot polja tabele, VLOOKUP izvede iskanje vrednosti v stolpcu B in vrne ustrezno vrednost iz stolpca 3 (stolpec D), 3000. Obvestilo nič (0) je naveden kot zadnji argument za vsiljevanje natančnega ujemanja.
Formula v G10 preprosto potegne rezultat iz H5:
=H5 // 3000
Za izvedbo povratnega iskanja je formula v H10:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Prefinjen bit je funkcija CHOOSE, ki se uporablja za preurejanje polja tabele tako, da je Cost prvi stolpec, Option pa zadnji:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Funkcija CHOOSE je namenjena izbiri vrednosti na podlagi številskega indeksa. V tem primeru podajamo tri vrednosti indeksa v konstanti matrike:
(3,2,1) // array constant
Z drugimi besedami, prosimo za stolpec 3, nato stolpec 2, nato stolpec 1. Temu sledijo trije obsegi, ki predstavljajo vsak stolpec tabele v vrstnem redu, kot so prikazani na delovnem listu.
S to konfiguracijo CHOOSE vrne vse tri stolpce v enem samem 2D matriku, kot je ta:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Če to matriko vizualiziramo kot tabelo na delovnem listu, imamo:
Opomba: naslovi niso del polja in so tukaj prikazani samo zaradi jasnosti.
V bistvu smo zamenjali stolpca 1 in 3. Reorganizirana tabela se vrne neposredno v VLOOKUP, ki se ujema s 3000, in vrne ustrezno vrednost iz stolpca 3, "C".
Z KAZALOM in UTEKOM
Zgornja rešitev deluje dobro, vendar jo je težko priporočiti, saj večina uporabnikov ne bo razumela, kako formula deluje. Boljša rešitev sta INDEX in MATCH z uporabo formule, kot je ta:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Tu funkcija MATCH najde vrednost 3000 v D5: D8 in vrne svoj položaj 3:
MATCH(G10,D5:D8,0) // returns 3
Opomba: MATCH je nastavljen za natančno ujemanje z nastavitvijo zadnjega argumenta na nič (0).
MATCH vrne rezultat neposredno v INDEX kot številko vrstice, zato formula postane:
=INDEX(B5:B8,3) // returns "C"
in INDEX vrne vrednost iz tretje vrstice B5: B8, "C".
Ta formula prikazuje, kako sta INDEX in MATCH lahko bolj prilagodljiva kot VLOOKUP.
Z XLOOKUP
XLOOKUP ponuja tudi zelo dobro rešitev. Enakovredna formula je:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Z iskalno vrednostjo iz G10 (3000), vsemi iskalnimi polji D5: D8 (stroški) in rezultatskim poljem B5: B8 (možnosti), XLOOKUP najde 3000 v iskalnem polju in vrne ustrezen element iz polja z rezultati, "C". Ker XLOOKUP privzeto izvede natančno ujemanje, načina ujemanja ni treba izrecno nastaviti.
