Két dimenziós keresés – VLOOKUP-INDEX-MATCH

Power BI Desktop alapok Hamarosan Megnézem Két Online délelőtt PowerQuery bevezető Hamarosan Megnézem Két online délelőtt Videó Megnézem - Excel makró alapok - Power BI alapok - Excel PIVOT mester Tanfolyamok - PowerQuery alapok

Előfordulhat olyan feladat, amikor egy táblázat első oszlopában és a fejlécében kell megkeresni egy-egy konkrét értéket, és a kettő által adott metszéspontban lévő adatot szeretnénk kivenni, úgy, ahogy ez az alábbi ábrán látható.

A feladat során lényeges, hogy mind az első oszlop, mind a fejléc egyedi adatsort tartalmaz, ahol nincs ismétlődő elem. Ez azért fontos, mert a kereséshez a közismert függvényeket fogjuk használni, amelyek alapesetben az első előfordulást keresik.

Két megoldást ismertetnék. Itt azonnal a megoldás látható, a videóban pedig ott  a magyarázat is.

1. VLOOKUP-MATCH páros (Fkeres-Hol.Van)

A lényeg az, hogy az egész táblára nézve a  ‘B’ oszlopban keressük VLOOKUP-al a kívánt autómárkát (Renault), és azt, hogy melyik oszlopból adja vissza az eredményt, nem egy konstans számmal adjuk meg, hanem a MATCH függvénnyel megkérdezzük, hogy a fejlécben a kívánt hónap (Március) hányadik.

1. lépés:  a VLOOKUP

=VLOOKUP("Renault";$B$2:$H$12;4;0)

A VLOOKUP megkeresi az első oszlopban a „Renault” szót, és a negyedik oszlopból visszaadja a 266-os értéket.

2. lépés: a MATCH

=MATCH("Március";$B$2:$H$2;0)

A MATCH megadja, hogy a fejlécben a „Március” hányadik

3. lépés: Egybe rakva

=VLOOKUP("Renault";$B$2:$H$12;MATCH("Március";$B$2:$H$2;0);0)

Az 1. lépésben lévő VLOOKUP  képletben a 4-es helyére beletettük a komlett MATCH részt.

INDEX-MATCH páros (Index-Hol.Van)

Ugyanezt a feladatot az INDEX függvénybe ágyazott két MATCH függvénnyel is meg lehet oldani. Ugyanis az INDEX pont azt tudja, amire itt szükségünk van, azaz egy táblázat adott számú sorában és adott számú oszlopának metszéspontjában lévő értéket ad vissza.

=INDEX(táblázat, sor_szám, [oszlop_szám])

Ezt a lehetőséget ritkábban használjuk, sokkal gyakoribb, hogy az INDEX csak egy dimenziós sorozatban (oszlopban) keres, és a második (oszlop) argumentumot meg sem adjuk.

Tehát ha az INDEX függvény sor és oszlop argumentumaiba MATCH függvényekkel kiszámoltatjuk azt, hogy a „Renault” hányadik az első oszlopban, és a „Március” hányadik az első sorban, akkor meg is kapjuk a kettő metszéspontjában lévő értéket.

=INDEX($B$2:$H$12;MATCH("Renault";$B$2:$B$12;0);MATCH("Március";$B$2:$H$2;0))

Letölthető minta dokumentum: Ketdimenzios-kereses

A videóban a megoldást interaktívvá tesszük, azaz választható az autómárka és a hónap, és a megoldást bemutatom Táblázattá alakított tartományon is.