V tomto tutoriálu se podíváme, jak společně použít funkce IFERROR a VLOOKUP k zachycení a zpracování různých chyb. Kromě toho se naučíte, jak v Excelu provádět postupné vlookupy vnořením několika funkcí IFERROR na sebe.

Excel VLOOKUP a IFERROR – tyto dvě funkce mohou být dost těžko pochopitelné samostatně, natož když jsou kombinovány. V tomto článku najdete několik přehledných příkladů, které se zabývají běžným použitím a jasně ilustrují logiku vzorců.

Pokud nemáte s funkcemi IFERROR a VLOOKUP mnoho zkušeností, možná bude dobré si nejprve zopakovat jejich základy podle výše uvedených odkazů.

• IFERROR s VLOOKUP pro zpracování chyb
  • VLOOKUP a vrácení vlastního textu místo chyby N/A
  • VLOOKUP a vrácení prázdné buňky nebo nuly, pokud není nic nalezeno
• Nest IFERROR. v rámci VLOOKUP, abyste vždy něco našli
• Sekvenční VLOOKUP s vnořenými IFERRORy

IFERROR vzorec VLOOKUP pro zpracování #N/A a dalších chyb

Když Excel VLOOKUP nenajde hledanou hodnotu, vyhodí chybu #N/A, například takto:

V závislosti na vašich obchodních potřebách můžete tuto chybu zamaskovat vlastním textem, nulou nebo prázdnou buňkou.

Příklad 1. Vzorec VLOOKUP Iferror pro nahrazení všech chyb vlastním textem

Pokud chcete nahradit standardní zápis chyby vlastním textem, obalte vzorec VLOOKUP IFERROR a do 2. argumentu (hodnota_if_error) zadejte libovolný text, například „Nenalezeno“:

Při hodnotě vyhledávání v B2 v hlavní tabulce a rozsahu vyhledávání A2:B4 v tabulce vyhledávání má vzorec následující tvar:

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

Následující snímek obrazovky ukazuje náš vzorec Excel IFERROR VLOOKUP v akci:

Výsledek vypadá mnohem srozumitelněji a mnohem méně děsivě, že?

Podobným způsobem můžete použít INDEX MATCH společně s IFERROR:

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

Vzorce IFERROR INDEX MATCH je užitečný zejména tehdy, když chcete vytáhnout hodnoty ze sloupce, který leží vlevo od sloupce vyhledávání (levé vyhledávání), a vrátit vlastní text, když nic nenajdete.

Příklad 2. IFERROR s VLOOKUP pro vrácení prázdného pole nebo 0, pokud není nic nalezeno

Pokud nechcete nic zobrazit, když není nalezena hodnota vyhledávání, nechte IFERROR zobrazit prázdný řetězec („“):

V našem příkladu vzorec vypadá následovně:

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

Jak vidíte, nevrací nic, když vyhledaná hodnota není v seznamu.

Pokud chcete chybu nahradit nulovou hodnotou, vložte do posledního argumentu 0:

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

Slovo nastrahy! Funkce IFERROR aplikace Excel zachytí všechny druhy chyb, nejen #N/A. Je to dobře nebo špatně? Vše závisí na vašem cíli. Pokud chcete zamaskovat všechny možné chyby, je IFERROR Vlookup správnou volbou. V mnoha situacích to však může být nerozumná technika.

Příklad pokud jste vytvořili pojmenovaný rozsah pro data v tabulce a ve vzorci Vlookup jste tento název špatně napsali, funkce IFERROR zachytí chybu #NAME? a nahradí ji textem „Nenalezeno“ nebo jiným textem, který zadáte. Výsledkem je, že se nikdy nemusíte dozvědět, že váš vzorec poskytuje špatné výsledky, pokud si překlepu sami nevšimnete. V takovém případě by bylo rozumnější zachytávat pouze chyby #N/A. K tomu slouží vzorec IFNA VLOOKUP v aplikaci Excel pro Office 365, Excel 209, Excel 2016 a Excel 2013, IF ISNA VLOOKUP ve všech verzích aplikace Excel.

Pointa zní: buďte velmi opatrní při výběru společníka pro váš vzorec VLOOKUP 🙂

Vložte IFERROR do VLOOKUP, abyste vždy něco našli

Představte si následující situaci: hledáte určitou hodnotu v seznamu a nenajdete ji. Jaké máte možnosti? Buď dostanete chybu N/A, nebo zobrazíte vlastní zprávu. Vlastně je tu ještě třetí možnost – pokud váš primární vlookup klopýtne, pak hledejte něco jiného, co tam určitě je!“

Pokračujme v našem příkladu dále, vytvořme pro naše uživatele nějaký informační panel, který jim zobrazí interní číslo konkrétní kanceláře. Něco takového:

Jak tedy vytáhnout přípojku ze sloupce B na základě čísla kanceláře v D2? Pomocí tohoto běžného vzorce Vlookup:

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

A bude to krásně fungovat, pokud vaši uživatelé zadají platné číslo v D2. Ale co když uživatel zadá nějaké neexistující číslo? V takovém případě ať zavolá na centrálu! Pro tento případ vložíte výše uvedený vzorec do argumentu value IFERROR a do argumentu value_if_error vložíte další Vlookup.

Kompletní vzorec je trochu dlouhý, ale funguje perfektně:

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

Pokud je číslo kanceláře nalezeno, uživatel dostane odpovídající interní číslo:

Pokud číslo kanceláře není nalezeno, zobrazí se interní číslo ústředny:

Aby byl vzorec trochu kompaktnější, můžete použít jiný přístup:

Nejprve zkontrolujte, zda se číslo v D2 nachází ve sloupci vyhledávání (všimněte si prosím, že jsme nastavili col_index_num na 1, aby vzorec vyhledal a vrátil hodnotu ze sloupce A): VLOOKUP(D2,$A$2:$B$7,1,FALSE)

Pokud zadané číslo úřadu nenajdeme, hledáme řetězec „centrální úřad“, který se v seznamu pro vyhledávání určitě nachází. Za tímto účelem zabalíme první VLOOKUP do IFERROR a celou tuto kombinaci vnoříme dovnitř další funkce VLOOKUP:

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

No, trochu jiný vzorec, stejný výsledek:

Ale jaký je důvod hledat „ústřední kancelář“, můžete se mě zeptat. Proč nezadat číslo přípojky přímo v IFERROR? Protože přípona se může někdy v budoucnu změnit. Pokud se tak stane, budete muset údaje ve zdrojové tabulce aktualizovat pouze jednou, aniž byste se museli starat o aktualizaci jednotlivých vzorců VLOOKUP.

Jak provádět sekvenční VLOOKUPy v Excelu

V situacích, kdy potřebujete v Excelu provádět tzv. sekvenční nebo zřetězené VLOOKUPy v závislosti na tom, zda předchozí vyhledávání bylo úspěšné nebo neúspěšné, vnořte do sebe dvě nebo více funkcí IFERROR a spouštějte VLOOKUPy postupně:

Formule pracuje s následující logikou:

Pokud první VLOOKUP nic nenajde, první IFERROR zachytí chybu a spustí další VLOOKUP. Pokud druhý VLOOKUP selže, druhý IFERROR zachytí chybu a spustí třetí VLOOKUP atd. Pokud všechny Vlookupy klopýtnou, poslední IFERROR vrátí vaši zprávu.

Tento vnořený vzorec IFERROR je užitečný zejména tehdy, když musíte Vlookup provádět na více listech, jak je uvedeno v následujícím příkladu.

Řekněme, že máte tři seznamy stejnorodých údajů na třech různých listech (v tomto příkladu čísla kanceláří) a chcete získat přípony pro určité číslo.

Předpokládáme-li, že hodnota vyhledávání je v buňce A2 v aktuálním listu a rozsah vyhledávání je A2:B5 ve třech různých pracovních listech (Sever, Jih a Západ), následující vzorec funguje na jedničku:

=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")))

Náš „zřetězený vzorec VLOOKUP“ tedy prohledá všechny tři listy v pořadí, v jakém jsme je do vzorce vnořili, a přinese první nalezenou shodu:

Takto se v Excelu používá IFERROR s VLOOKUP. Děkuji vám za přečtení a doufám, že se příští týden uvidíme na našem blogu!

K dispozici ke stažení

Příklady vzorce IFERROR VLOOKUP v Excelu

Mohlo by vás také zajímat

• Funkce IFERROR v Excelu s příklady vzorců
• Použití IF s VLOOKUP v Excelu
• Ukázka VLOOKUP v Excelu pro začátečníky

.