SVYHLEDAT pro přiřazení více sloupců najednou

Autor: Miroslav Lorenc

Pokud používáte častěji vyhledávací funkci SVYHLEDAT (VLOOKUP), tak jste se jistě setkali s případem, kdy na základě jednoho klíče doplňujete více údajů ze zdrojové tabulky. Zde si ukážeme, jak si takový případ zjednodušit. Dejme tomu, že v našem demonstrativním příkladu máme seznam zaměstnanců ve sloupcích A až F a do tabulky v oblasti H1:M2 chceme vypsat údaje o vybraném zaměstnanci – toho identifikujeme pomocí osobního čísla.

Doplnění údajů pokud jsou sloupce ve stejném pořadí

Pokud do buňky H2 zadáme osobní číslo zaměstnance, tak ostatní údaje o daném zaměstnanci snadno doplníme pomocí funkce SVYHLEDAT /(VLOOKUP). Jméno bychom tedy dohledali pomocí

• =SVYHLEDAT(H2;A:F;2;0)

Obdobně pro příjmení, rodné číslo, pojišťovnu a mobil. Funkce by se lišily pouze v třetím argumentu, kde by byla pro příjmení místo dvojky trojka apod. V případě, že ze seznamu zaměstnanců budeme takto do druhé tabulky vypisovat 50 sloupců, bude zápis funkcí zdlouhavý a nejspíš bychom někde udělali chybu. Zjednodušit si to můžeme tak, že místo čísla sloupce použijeme vnořenou funkci SLOUPEC (COLUMN), která nám při použití relativního odkazu vrátí řadu čísel s přírůstkem jedné, což právě potřebujeme.

• =SVYHLEDAT(H2;A:F;SLOUPEC(B1);0)

Funkci upravíme tak, abychom ji mohli kopírovat doprava:

• =SVYHLEDAT($H2;$A:$F;SLOUPEC(B1);0)

Pokud máte ve svém Excelu k dispozici funkci XLOOKUP, určitě se podívejte na řešení obdobného příkladu: XLOOKUP pro více sloupců najednou. A nyní (pokud je pořadí sloupců v obou tabulkách stejné) již vše funguje jednodušeji.

Doplnění údajů pokud jsou sloupce na přeskáčku

V druhém příkladu si ukážeme, jak postupovat, pokud je pořadí sloupců v druhé tabulce rozdílné než u zdrojové. Zde je os. číslo, jméno a příjmení stejně, ale poté je mobil, pojišťovna a rodné číslo na přeskáčku.

V tomto případě můžeme použít zápis matice čísel v třetím argumentu funkce SVYHLEDAT. Matici v české verzi Excelu zapíšeme jako {2\3\6\5\4}, v anglické verzi jako {2,3,6,5,4}.

• {=SVYHLEDAT(H2;A:F;{2\3\6\5\4};0)}

Jak bychom měli postupovat:

1. Do H2 zapíšeme libovolné os. číslo zaměstnance.
2. Označíme buňky I2:M2.
3. Zapíšeme =SVYHLEDAT(H2;A:F;{2\3\6\5\4};0) – tj. s relativními adresami a bez úvodní a koncové složené závorky.
4. Vzorec potvrdíme pomocí CTRL+SHIFT+ENTER – tj. jako maticový vzorec.

Ve všech buňkách I2:M2 bychom měli mít stejný vzorec a nemělo by jít upravit jednotlivé buňky – pouze všechny najednou. To je jedna z výhod maticových vzorců, o kterých se více rozepíšeme v jiném článku.

Další variantou řešení je využité funkce vnořené POZVYHLEDAT (MATCH) namísto předešlého využití matice. Pro správné fungování jednotného vzorce a jeho kopírování musíme ještě přidat správné smíšené a absolutní adresy.

• =SVYHLEDAT($H2;$A:$F;POZVYHLEDAT(I$1;$A$1:$F$1;0);0)

Soubor s výsledkem ke stažení