Generička formula
=VLOOKUP(A1,CHOOSE((1,2),range2,range1),2,0)
Sažetak
Da biste pomoću VLOOKUP-a izvršili pregled s lijeve strane, možete koristiti funkciju CHOOSE za preuređivanje tablice pretraživanja. U prikazanom primjeru formula u F5 je:
=VLOOKUP(E5,CHOOSE((1,2),score,rating),2,0)
pri čemu su bodovi (C5: C9) i ocjena (B5: B9) imenovani rasponi.
Obrazloženje
Jedno od ključnih ograničenja funkcije VLOOKUP je da može tražiti samo vrijednosti udesno. Drugim riječima, stupac koji sadrži vrijednosti pretraživanja mora se nalaziti lijevo od vrijednosti koje želite dohvatiti pomoću VLOOKUP-a. Ne postoji način da se poništi ovo ponašanje jer je ono ugrađeno u funkciju. Kao rezultat, s normalnom konfiguracijom, način na koji VLOOKUP ne može potražiti ocjenu u stupcu B na temelju rezultata u stupcu C.
Jedno zaobilazno rješenje je restrukturiranje same tablice pretraživanja i pomicanje stupca pretraživanja lijevo od vrijednosti pretraživanja. To je pristup u ovom primjeru koji koristi obrnutu ocjenu funkcije IZBORI i ocjenjuje ovako:
CHOOSE((1,2),score,rating)
Obično se CHOOSE koristi s jednim indeksnim brojem kao prvi argument, a preostali su argumenti vrijednosti između kojih možete odabrati. Međutim, ovdje dajemo konstantu niza za indeksni broj koji sadrži dva broja: (1,2). U osnovi tražimo da odaberemo i prvu i drugu vrijednost.
Vrijednosti su dane u dva navedena raspona u primjeru: rezultat i ocjena. Međutim, primijetite da ove domete pružamo obrnutim redoslijedom. Funkcija CHOOSE odabire oba raspona navedenim redoslijedom i vraća rezultat kao jedan niz poput ovog:
(5,"Excellent";4,"Good";3,"Average";2,"Poor";1,"Terrible")
CHOOSE vraća ovaj niz izravno u VLOOKUP kao argument polja tablice. Drugim riječima, CHOOSE isporučuje tablicu pretraživanja poput ove VLOOKUP-u:
Koristeći vrijednost pretraživanja u E5, VLOOKUP pronalazi podudaranje unutar novostvorene tablice i vraća rezultat iz drugog stupca.
Preuređivanje s konstantom niza
U prikazanom primjeru preuređujemo tablicu pretraživanja preokrećući "ocjenu" i "ocjenu" unutar odabrane funkcije. Međutim, umjesto toga bismo mogli koristiti konstantu niza za ovako preuređivanje:
CHOOSE((2,1),rating,score)
Rezultat je potpuno isti.
Uz INDEKS i UTAKMICU
Iako gornji primjer dobro funkcionira, nije idealan. Kao prvo, većina prosječnih korisnika neće razumjeti kako formula djeluje. Prirodnije rješenje su INDEKS i UTAKMICA. Evo ekvivalentne formule:
=INDEX(rating,MATCH(E5,score,0))
Zapravo, ovo je dobar primjer kako su INDEX i MATCH fleksibilniji od VLOOKUP-a.
