Már elég régen nem képletezgettünk az OfficeGuru berkein belül, úgyhogy a mai posztban egy olyan kis fejtörőt fogunk körbejárni, amibe a mindennapok során könnyen belefuthatunk, hiszen a VLOOKUP kétség nélkül az egyik leggyakrabban használt Excel-függvény. Mi történik akkor, ha a VLOOKUP függvényünkkel keresett értékünket a formula többször megtalálja a meghatározott tömbben? Alapvetően mindig az első értéket fogja nekünk visszaadni, ami akár meg is tévesztheti a táblánkat később elemző kollégákat, felhasználókat.

Úgyhogy most egy olyan megoldást nézünk meg, amelynek segítségével ilyen esetekben listázható az összes találat és azzal sem árulok el valószínűleg nagy titkot, ha már előre megírom, nem a VLOOKUP függvény fog nekünk segíteni.

Szóval adott a következő táblázat:

Értelemszerűen a Város oszlop első cellájában szereplő városhoz szeretnénk az összes értéket bekeresni, nem csak az elsőt. Tömbfüggvény lesz a megoldás, amelynek "motorja" egy IF formula lesz, amelyben azt vizsgáljuk meg, hogy városneveink között melyek azok, amelyek egyeznek az eredetileg megadottal:

IF($H$5=$D$4:$D$14,

Ennek a vizsgálatnak az eredménye egy TRUE illetve FALSE értékeket tartalmazó tömb lesz majd, pontosan annyi ilyen értékkel, ahány város van az eredeti listánkban.

Ezt folytatva, most az a két paraméter következik az IF-ből, ami egyrészt meghatározza, hogy mi történjen a feltétel teljesülése esetén, másrészt ami megmondja, hogy mi történjen a feltétel nem teljesülésekor.

Értelemszerűen a nem teljesülés elég egyszerű, szimplán maradjon üresen a mező, hiszen nincs egyező értékünk a városnevek között, viszont ha teljesül, akkor most a sorszámokat kell begyűjtenünk. Vigyázzunk, mert Kecskemét ránézésre a 4. sorban van, valójában viszont a meghivatkozott város-tömbünk első sorában!

Ennek megfelelően az IF második paramétereként a

ROW($D$4:$D$14)

nem lesz jó, mert ez egy ilyen tömböt hozna létre:

{4,5,6,7,8,9,10,11,12,13,14},

ami később rossz értékek bekereséséhez vezethet, hiszen a tömb soraira kell koncentrálnunk.

Azaz egy olyan tömböt kell létrehoznunk, ami így nézne ki:

{1,2,3,4,5,6,8,9,10}

Értelemszerűen a tömbben az összes sorszám most csak a példa kedvéért szerepel, valójában mondjuk EGER város esetén a tömb így néz ki:

{4,7,9}

Szóval a megfelelő tömböt egy egyszerű logikai művelettel fogjuk megszerezni, azaz minden sorszámból kivonjuk a legalacsonyabb sorszám értékét, majd minden eredményhez hozzáadunk egyet:

ROW($D$4:$D$14)-MIN(ROW($D$4:$D$14))+1

Azaz kész az IF függvényünk is:

IF($H$5=$D$4:$D$14,ROW($D$4:$D$14)-MIN(ROW($D$4:$D$14))+1,"")

Mit fog tenni? Ahogy már végigmentünk rajta, ha a $D$4:$D$14 táblában van a megadott városnévvel megegyező érték, akkor kiírja az adott sorok számát egy tömbbe.

Az okoskodásnak még nincs vége, hiszen most van egy tömbbünk 1-2-3 stb. sorszámmal, azoknak az értékeit be kellene keresnünk.

Még mindig a ROW függvényt használjuk összehozva egy SMALL függvénnyel, amely utóbbi az első paramétereként megadott tartományból veszi ki a második paramétereként megadott legkisebb értéket:

Pl.: SMALL(A1:A5,4) - az A1:A5 tartomány 4. legkisebb értékét adja vissza.

És ha a ROW függvényt simán bármelyik oszlop első cellájára állítjuk, akkor egy tömbfüggvény esetén ha lehúzzuk a formulánkat, akkor értelemszerűen a ROW eredménye is növekszik (azaz egyről kezdünk majd jön a második és harmadik legkisebb érték):

SMALL(IF($H$5=$D$4:$D$14,ROW($D$4:$D$14)-MIN(ROW($D$4:$D$14))+1,""),ROW(L1))

Azaz látható, hogy az IF formulánk által visszaadott tömbből először a legkisebb sorszámot, majd a következő és az azután következő értékeket fogja ez a tömbfüggvény visszaadni, értelemszerűen növekvő sorrendben.

Ergó ha azt a tömböt nézzük, amit az IF-nél beszéltünk Eger kapcsán:

{4,7,9}

Akkor a SMALL(....,ROW(L1)) először a 4-est fogja listázni, aztán a hetes sort, majd végül a kilencest.

De ez még mindig elég, mert még az értékeket is vissza kell kapnunk, amire tökéletes választás lesz az INDEX függvény, hiszen azt már ezerszer átbeszéltük, hogy ennek a formulának a segítségével egy tábla megadott sorában, oszlopában vagy sor és oszlop kereszteződésében található értéket kaphatunk vissza.

Ezzel pedig össze is állhat, hogy mi lesz a végső eredményünk - az INDEX első paramétereként megadjuk a városokhoz tartozó bevételek tömbjét, második paramétereként pedig a fentebb már kielemzett SMALL-IF egybeágyazást és így az eredményünk tömbfüggvényként lehúzva mindhárom EGER értéket visszadobja.

={INDEX($E$4:$E$14,SMALL(IF($H$5=$D$4:$D$14,ROW($D$4:$D$14)-MIN(ROW($D$4:$D$14))+1,""),ROW(B1)))}