Ebben a bemutatóban az IFERROR és VLOOKUP funkciók együttes használatát fogjuk megvizsgálni a különböző hibák csapdázására és kezelésére. Ezen túlmenően megtanuljuk, hogyan végezhetünk szekvenciális vlookupokat az Excelben több IFERROR függvény egymásra fészkelésével.

Excel VLOOKUP és IFERROR – ezt a két függvényt külön-külön is elég nehéz lehet megérteni, nemhogy akkor, amikor kombinálják őket. Ebben a cikkben talál néhány könnyen követhető példát, amelyek a leggyakoribb felhasználási módokra vonatkoznak, és világosan szemléltetik a képletek logikáját.

Ha nincs sok tapasztalata az IFERROR és a VLOOKUP függvényekkel kapcsolatban, érdemes először feleleveníteni az alapjaikat a fenti linkek segítségével.

• IFERROR a VLOOKUP-pal a hibák kezelésére
  • VLOOKUP és saját szöveg visszaadása az N/A hiba helyett
  • VLOOKUP és üres cella vagy nulla visszaadása, ha nem találtunk semmit
• Nest IFERROR. a VLOOKUP-on belül, hogy mindig találjon valamit
• Soros Vlookupok egymásba ágyazott IFERROR-okkal

IFERROR VLOOKUP formula a #N/A és egyéb hibák kezelésére

Ha az Excel Vlookup nem talál keresési értéket, #N/A hibát dob, például a következőt:

Az üzleti igényektől függően a hibát saját szöveggel, nullával vagy üres cellával álcázhatja.

1. példa. Iferror Vlookup-képlet az összes hiba saját szöveggel való helyettesítéséhez

Ha a szabványos hiba jelölését saját szöveggel szeretné helyettesíteni, tekerje a VLOOKUP-képletet IFERROR-ba, és a 2. argumentumba (value_if_error) írjon be bármilyen szöveget, például “Nem találtuk”:

Azzal, hogy a keresési érték a Fő táblázatban B2-ben van, a keresési tartomány pedig A2:B4 a Keresési táblázatban, a képlet a következő alakot veszi fel:

=IFERROR(VLOOKUP(B2,'Lookup table'!$A:$B, 2, FALSE), "Not found")

Az alábbi képernyőkép az Excel IFERROR VLOOKUP képletünket mutatja működés közben:

Az eredmény sokkal érthetőbbnek és sokkal kevésbé félelmetesnek tűnik, nem igaz?

Hasonlóképpen használhatjuk az INDEX MATCH-ot az IFERROR-ral együtt:

=IFERROR(INDEX('Lookup table'!$B:$B,MATCH(B2,'Lookup table'!$A:$A,0)), "Not found")

Az IFERROR INDEX MATCH formula különösen akkor hasznos, ha a keresési oszloptól balra eső oszlopból akarunk értékeket húzni (bal oldali keresés), és a saját szövegünket adjuk vissza, ha nem találunk semmit.

2. példa. IFERROR a VLOOKUP-pal, hogy üres vagy 0 értéket adjon vissza, ha nem talált semmit

Ha nem akar semmit sem mutatni, ha a keresési értéket nem találja, akkor az IFERROR egy üres karakterláncot (“”) jelenítsen meg:

Példánkban a képlet a következőképpen néz ki:

=IFERROR(VLOOKUP(B2,'Lookup table'!$A:$B, 2, FALSE), "")

Amint látható, semmit sem ad vissza, ha a keresési érték nincs a keresési listában.

Ha a hibát nullás értékkel szeretnénk helyettesíteni, akkor az utolsó argumentumba írjunk 0-t:

=IFERROR(VLOOKUP(B2,'Lookup table'!$A:$B, 2, FALSE), 0)

Vigyázat! Az Excel IFERROR függvénye mindenféle hibát elkap, nem csak az #N/A-t. Ez jó vagy rossz? Minden a céltól függ. Ha minden lehetséges hibát ki akarsz takarni, akkor az IFERROR Vlookup a megfelelő megoldás. Sok helyzetben azonban nem biztos, hogy bölcs technika.

Ha például a táblázat adataihoz létrehozott egy névvel ellátott tartományt, és a Vlookup-képletben elírta a nevet, az IFERROR elkapja a #NAME? hibát, és a “Nem található” vagy bármely más, Ön által megadott szöveggel helyettesíti azt. Ennek eredményeképpen soha nem tudhatja meg, hogy a képlet rossz eredményt ad, hacsak nem veszi észre a helyesírási hibát. Ilyen esetben ésszerűbb lenne, ha csak a #N/A hibákat csapdáznánk. Ehhez használjon IFNA Vlookup képletet az Excel for Office 365, Excel 209, Excel 2016 és Excel 2013 programokban, IF ISNA VLOOKUP-ot minden Excel verzióban.

A lényeg: legyen nagyon óvatos a VLOOKUP képlet társának kiválasztásakor 🙂

Fészkelje be az IFERROR-t a VLOOKUP-on belül, hogy mindig találjon valamit

Képzelje el a következő helyzetet: keres egy adott értéket egy listában, és nem találja. Milyen lehetőségeid vannak? Vagy kap egy N/A hibát, vagy megjeleníti a saját üzenetét. Valójában van egy harmadik lehetőség is – ha az elsődleges vlookupod megbotlik, akkor keress valami mást, ami biztosan ott van!

A példánkat tovább folytatva, hozzunk létre valamilyen műszerfalat a felhasználóink számára, amely megmutatja nekik egy adott iroda mellékszámát. Valahogy így:

Hogyan húzzuk ki a melléket a B oszlopból a D2-ben lévő irodaszám alapján? Ezzel a szabályos Vlookup-képlettel:

=VLOOKUP($D,$A:$B,2,FALSE)

És ez szépen fog működni, amíg a felhasználók érvényes számot adnak meg a D2-ben. De mi van akkor, ha egy felhasználó olyan számot ad be, amely nem létezik? Ebben az esetben hívják fel a központi irodát! Ehhez a fenti képletet ágyazd be az IFERROR value argumentumába, és a value_if_error argumentumba tegyél egy másik Vlookupot.

A teljes formula egy kicsit hosszú, de tökéletesen működik:

=IFERROR(VLOOKUP("office "&$D,$A:$B,2,FALSE),VLOOKUP("central office",$A:$B,2,FALSE))

Ha a hivatal száma megvan, a felhasználó megkapja a megfelelő mellékszámot:

Ha a hivatal száma nem található, a központi iroda mellékét jeleníti meg:

Hogy a képletet egy kicsit tömörebbé tegyük, használhatunk egy másik megközelítést:

Először is ellenőrizzük, hogy a D2-ben szereplő szám szerepel-e a keresési oszlopban (kérjük, vegye figyelembe, hogy a col_index_num értékét 1-re állítottuk, hogy a képlet az A oszlopból keressen és adjon vissza értéket): VLOOKUP(D2,$A$2:$B$7,1,FALSE)

Ha a megadott irodaszámot nem találjuk, akkor a “központi iroda” karakterláncot keressük, amely biztosan szerepel a keresési listában. Ehhez az első VLOOKUP-ot IFERROR-ba csomagoljuk, és ezt az egész kombinációt egy másik VLOOKUP függvénybe fészkeljük:

=VLOOKUP(IFERROR(VLOOKUP(D2,$A:$B,1,FALSE),"central office"),$A:$B,2)

Hát egy kicsit más képlet, ugyanaz az eredmény:

De mi az oka annak, hogy a “központi irodát” keressük, kérdezheti tőlem. Miért nem adjuk meg a mellékszámot közvetlenül az IFERROR-ban? Mert a mellékszám a jövőben valamikor megváltozhat. Ha ez megtörténik, akkor csak egyszer kell frissítenie az adatokat a forrástáblában, anélkül, hogy minden egyes VLOOKUP-képlet frissítésével kellene foglalkoznia.

Hogyan végezzen szekvenciális VLOOKUP-okat az Excelben

Azokban a helyzetekben, amikor úgynevezett szekvenciális vagy láncolt Vlookup-okat kell végrehajtania az Excelben attól függően, hogy egy korábbi keresés sikeres vagy sikertelen volt-e, fészkeljen be két vagy több IFERROR függvényt a Vlookup-ok egyenkénti futtatásához:

A képlet a következő logikával működik:

Ha az első VLOOKUP nem talál semmit, az első IFERROR hibát jelez, és egy újabb VLOOKUP-ot futtat. Ha a második VLOOKUP nem talál semmit, a második IFERROR hibát fog, és lefuttatja a harmadik VLOOKUP-ot, és így tovább. Ha az összes VlookUP megbotlik, az utolsó IFERROR visszaadja az üzenetét.

Ez a beágyazott IFERROR formula különösen akkor hasznos, ha több lapon kell VlookUP-ot végeznie, ahogy az alábbi példában látható.

Tegyük fel, hogy három különböző munkalapon (ebben a példában irodai számok) három homogén adatokból álló listával rendelkezik, és egy bizonyos számhoz szeretne egy melléket kapni.

Föltételezve, hogy a keresési érték az aktuális lap A2 cellájában van, és a keresési tartomány A2:B5 a 3 különböző munkalapon (Észak, Dél és Nyugat), a következő képlet remekül működik:

=IFERROR(VLOOKUP(A2,North!$A:$B,2,FALSE), IFERROR(VLOOKUP(A2,South!$A:$B,2,FALSE), IFERROR(VLOOKUP(A2,West!$A:$B,2,FALSE),"Not found")))

A “láncolt Vlookup” képletünk tehát mindhárom lapon a képletbe ágyazott sorrendben keres, és az első talált egyezést hozza:

Így használja az IFERROR-t a VLOOKUP-pal az Excelben. Köszönöm, hogy elolvastad, és remélem, jövő héten találkozunk a blogon!

Az elérhető letöltések

Excel IFERROR VLOOKUP képlet példák

Az is érdekelheti

• Excel IFERROR függvény képlet példákkal
• IF használata VLOOKUP-pal az Excelben
• Excel VLOOKUP oktatóanyag kezdőknek