Általános képlet
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Összegzés
A VLOOKUP megfordításához - azaz az eredeti keresési érték megtalálásához a VLOOKUP képlet eredményével - használhat egy bonyolult képletet a CHOOSE függvény alapján, vagy egyszerűbb képleteket, amelyek az INDEX és a MATCH vagy az XLOOKUP alapján találhatók, az alábbiakban leírtak szerint. A bemutatott példában a H10 képlete a következő:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Ezzel a beállítással a VLOOKUP megtalálja a 3000-es költséghez társított opciót, és visszatér a "C" értékre.
Megjegyzés: ez egy fejlettebb téma. Ha most kezdi a VLOOKUP használatát, kezdje itt.
Bevezetés
A VLOOKUP kulcsfontosságú korlátozása, hogy csak jobbra kereshet értékeket. Más szavakkal, a keresési értékekkel rendelkező oszlopnak balra kell lennie a VLOOKUP segítségével lekérni kívánt értékektől. Ennek eredményeként a szokásos konfigurációval nincs mód a VLOOKUP használatára, hogy "balra nézzen" és megfordítsa az eredeti keresést.
A VLOOKUP szempontjából a problémát így vizualizálhatjuk:
Az alábbiakban ismertetett megoldás a VÁLASZTÁS funkcióval rendezi át az asztalt a VLOOKUP belsejében.
Magyarázat
Az elejétől kezdve a H5 képlete egy normál VLOOKUP képlet:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
A G5 keresési értékként ("C") és a B5: D8 táblában található adatoként tábla tömbként használva a VLOOKUP kikeresi a B oszlop értékeit, és a megfelelő értéket adja vissza a 3. oszlopból (D oszlop), 3000. A nulla (0) az utolsó argumentum a pontos egyezés kikényszerítésére.
A G10 képlete egyszerűen kihúzza az eredményt a H5-ből:
=H5 // 3000
A fordított kereséshez a H10 képlete a következő:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
A trükkös bit a CHOOSE függvény, amelyet a tábla tömb átrendezésére használnak úgy, hogy a Költség legyen az első oszlop, az Option pedig az utolsó:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
A KIVÁLASZTÁS funkció egy érték kiválasztására szolgál numerikus index alapján. Ebben az esetben három indexértéket adunk meg tömb konstansban:
(3,2,1) // array constant
Más szavakkal, a 3., majd a 2., majd az 1. oszlopot kérjük. Ezt követi a három tartomány, amelyek a táblázat minden oszlopát a munkalapon megjelenő sorrendben képviselik.
Ezzel a konfigurációval a CHOOSE mind a három oszlopot visszaadja egyetlen 2D tömbben, így:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Ha ezt a tömböt táblázatként jelenítjük meg a munkalapon, akkor:
Megjegyzés: a címsorok nem részei a tömbnek, és itt csak az érthetőség kedvéért láthatók.
Valójában felcseréltük az 1. és a 3. oszlopot. Az átszervezett tábla közvetlenül a VLOOKUP-ba kerül, amely megegyezik a 3000-vel, és a 3. oszlop "C" megfelelő értékét adja vissza.
INDEX-sel és MATCH-szal
A fenti megoldás remekül működik, de nehéz ajánlani, mivel a legtöbb felhasználó nem fogja megérteni a képlet működését. Jobb megoldás az INDEX és a MATCH, egy ilyen képlet segítségével:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Itt a MATCH függvény megtalálja a 3000 értéket a D5: D8 mezőben, és visszaadja a 3 pozíciót:
MATCH(G10,D5:D8,0) // returns 3
Megjegyzés: A MATCH pontos egyezésre van konfigurálva úgy, hogy az utolsó argumentumot nullára (0) állítja.
A MATCH az eredményt közvetlenül az INDEX-be adja vissza sorszámként, így a képlet a következő lesz:
=INDEX(B5:B8,3) // returns "C"
és az INDEX adja vissza az értéket a B5 harmadik sorából: B8, "C".
Ez a képlet megmutatja, hogy az INDEX és a MATCH hogyan lehet rugalmasabb, mint a VLOOKUP.
XLOOKUP-tal
Az XLOOKUP szintén nagyon jó megoldást nyújt. Az egyenértékű képlet:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
A G10 (3000) keresési értékével, a D5: D8 (költségek) összes tömbjével és a B5: B8 (opciók) eredménytömbjével az XLOOKUP megtalálja a 3000-et a keresési tömbben, és visszaadja a megfelelő elemet az eredménytömbből, "C". Mivel az XLOOKUP alapértelmezés szerint pontos egyezést végez, nem szükséges kifejezetten beállítani az egyezési módot.
