Všeobecný vzorec
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Zhrnutie
Ak chcete zvrátiť VLOOKUP, tj nájsť pôvodnú hodnotu vyhľadávania pomocou výsledku vzorca VLOOKUP, môžete použiť zložitý vzorec založený na funkcii CHOOSE alebo priamočiarejšie vzorce založené na indexoch INDEX a MATCH alebo XLOOKUP, ako je vysvetlené nižšie. V zobrazenom príklade je vzorec v H10:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
S týmto nastavením vyhľadá VLOOKUP možnosť spojenú s cenou 3000 a vráti „C“.
Poznámka: toto je pokročilejšia téma. Ak s VLOOKUPOM ešte len začínate, začnite tu.
Úvod
Kľúčovým obmedzením funkcie VLOOKUP je, že môže vyhľadávať hodnoty iba vpravo. Inými slovami, stĺpec s hodnotami vyhľadávania musí byť naľavo od hodnôt, ktoré chcete načítať pomocou funkcie VLOOKUP. Výsledkom je, že pri štandardnej konfigurácii neexistuje spôsob, ako použiť funkciu VLOOKUP na „pohľad doľava“ a obrátenie pôvodného vyhľadávania.
Z hľadiska VLOOKUP môžeme problém vizualizovať takto:
Riešenie vysvetlené nižšie využíva funkciu CHOOSE na nové usporiadanie tabuľky vo vnútri VLOOKUP.
Vysvetlenie
Od začiatku je vzorec v H5 normálny vzorec VLOOKUP:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Použitím G5 ako vyhľadávacej hodnoty („C“) a údajov v B5: D8 ako matici tabuľky vykoná VLOOKUP vyhľadanie hodnôt v stĺpci B a vráti zodpovedajúcu hodnotu zo stĺpca 3 (stĺpec D), 3000. Poznámka nula (0) je uvedená ako posledný argument na vynútenie presnej zhody.
Vzorec v G10 jednoducho stiahne výsledok z H5:
=H5 // 3000
Na vykonanie spätného vyhľadávania je vzorec v H10:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Zložitým bitom je funkcia CHOOSE, ktorá sa používa na nové usporiadanie poľa tabuľky, takže Cost je prvý stĺpec a Option je posledný:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Funkcia CHOOSE slúži na výber hodnoty na základe číselného indexu. V tomto prípade dodávame tri hodnoty indexu v konštante poľa:
(3,2,1) // array constant
Inými slovami, požadujeme stĺpec 3, potom stĺpec 2 a potom stĺpec 1. Nasledujú tri rozsahy, ktoré predstavujú každý stĺpec tabuľky v poradí, v akom sa nachádzajú na pracovnom hárku.
S touto konfiguráciou CHOOSE vráti všetky tri stĺpce v jednom 2D poli takto:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Ak si toto pole vizualizujeme ako tabuľku v hárku, máme:
Poznámka: nadpisy nie sú súčasťou poľa a sú tu zobrazené iba kvôli prehľadnosti.
Účinne sme vymenili stĺpce 1 a 3. Reorganizovaná tabuľka sa vráti priamo do programu VLOOKUP, ktorý zodpovedá 3000, a vráti zodpovedajúcu hodnotu zo stĺpca 3 „C“.
S INDEXOM a ZÁPASOM
Vyššie uvedené riešenie funguje dobre, ale je ťažké ho odporučiť, pretože väčšina používateľov nechápe, ako vzorec funguje. Lepšie riešenie je INDEX a MATCH pomocou tohto vzorca:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Funkcia MATCH tu nájde hodnotu 3000 v D5: D8 a vráti svoju pozíciu 3:
MATCH(G10,D5:D8,0) // returns 3
Poznámka: MATCH je nakonfigurovaný na presnú zhodu nastavením posledného argumentu na nulu (0).
MATCH vráti výsledok priamo na INDEX ako číslo riadku, takže vzorec bude:
=INDEX(B5:B8,3) // returns "C"
a INDEX vráti hodnotu z tretieho radu B5: B8, „C“.
Tento vzorec ukazuje, ako môžu byť INDEX a MATCH flexibilnejšie ako VLOOKUP.
S XLOOKUPOM
XLOOKUP tiež poskytuje veľmi dobré riešenie. Ekvivalentný vzorec je:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
S vyhľadávacou hodnotou od G10 (3000), al ookupovým poľom D5: D8 (náklady) a výsledkovým poľom B5: B8 (voliteľné), XLOOKUP vyhľadá 3000 vo vyhľadávacom poli a vráti príslušnú položku z výsledkového poľa, „C“. Pretože program XLOOKUP predvolene vykonáva presnú zhodu, nie je potrebné explicitne nastavovať režim zhody.
