Obecný vzorec
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
souhrn
Chcete-li obrátit SVYHLEDÁNÍ - tj. Najít původní hodnotu vyhledávání pomocí výsledku vzorce SVYHLEDAT - můžete použít složitý vzorec založený na funkci VÝBĚR nebo přímější vzorce založené na INDEXU a MATCH nebo XLOOKUPU, jak je vysvětleno níže. V zobrazeném příkladu je vzorec v H10:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
S tímto nastavením vyhledá VLOOKUP možnost spojenou s cenou 3000 a vrátí „C“.
Poznámka: toto je pokročilejší téma. Pokud s VLOOKUP teprve začínáte, začněte zde.
Úvod
Klíčovým omezením VLOOKUPu je, že může vyhledávat pouze hodnoty napravo. Jinými slovy, sloupec s hodnotami vyhledávání musí být nalevo od hodnot, které chcete načíst pomocí VLOOKUP. Výsledkem je, že se standardní konfigurací neexistuje způsob, jak použít VLOOKUP k „pohledu vlevo“ a ke zrušení původního vyhledávání.
Z hlediska VLOOKUP můžeme problém vizualizovat takto:
Řešení vysvětlené níže používá funkci CHOOSE k přeskupení tabulky uvnitř VLOOKUP.
Vysvětlení
Počínaje začátkem je vzorec v H5 normální vzorec VLOOKUP:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Při použití G5 jako vyhledávací hodnoty („C“) a dat v B5: D8 jako matici tabulky provede VLOOKUP vyhledání hodnot ve sloupci B a vrátí odpovídající hodnotu ze sloupce 3 (sloupec D), 3000. Upozornění nula (0) je uvedena jako poslední argument k vynucení přesné shody.
Vzorec v G10 jednoduše vytáhne výsledek z H5:
=H5 // 3000
Chcete-li provést zpětné vyhledávání, je vzorec v H10:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Složitým bitem je funkce CHOOSE, která se používá k přeskupení pole tabulky tak, že Cost je první sloupec a Option je poslední:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Funkce CHOOSE slouží k výběru hodnoty na základě číselného indexu. V tomto případě dodáváme tři hodnoty indexu v maticové konstantě:
(3,2,1) // array constant
Jinými slovy, žádáme o sloupec 3, poté sloupec 2 a poté sloupec 1. Za ním následují tři rozsahy, které představují každý sloupec tabulky v pořadí, v jakém se objevují na listu.
S touto konfigurací vrátí CHOOSE všechny tři sloupce v jednom 2D poli takto:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Pokud si toto pole představíme jako tabulku v listu, máme:
Poznámka: nadpisy nejsou součástí pole a jsou zde zobrazeny pouze pro přehlednost.
Účinně jsme vyměnili sloupce 1 a 3. Reorganizovaná tabulka se vrátí přímo do VLOOKUP, která odpovídá 3000, a vrátí odpovídající hodnotu ze sloupce 3 „C“.
S INDEXEM a ZÁPASEM
Výše uvedené řešení funguje dobře, ale je těžké ho doporučit, protože většina uživatelů nechápe, jak vzorec funguje. Lepším řešením je INDEX a MATCH pomocí vzorce jako je tento:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Zde funkce MATCH najde hodnotu 3000 v D5: D8 a vrátí svou pozici 3:
MATCH(G10,D5:D8,0) // returns 3
Poznámka: MATCH je nakonfigurován pro přesnou shodu nastavením posledního argumentu na nulu (0).
MATCH vrací výsledek přímo na INDEX jako číslo řádku, takže vzorec se stane:
=INDEX(B5:B8,3) // returns "C"
a INDEX vrací hodnotu ze třetí řady B5: B8, „C“.
Tento vzorec ukazuje, jak INDEX a MATCH mohou být flexibilnější než VLOOKUP.
S XLOOKUPEM
XLOOKUP také poskytuje velmi dobré řešení. Ekvivalentní vzorec je:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
S vyhledávací hodnotou z G10 (3000), al ookupovým polem D5: D8 (náklady) a výsledkovým polem B5: B8 (možnosti), XLOOKUP vyhledá 3000 ve vyhledávacím poli a vrátí odpovídající položku z výsledkového pole, "C". Protože XLOOKUP ve výchozím nastavení provádí přesnou shodu, není nutné explicitně nastavovat režim shody.
