Jak používat VLOOKUP v Excelu: pokročilé příklady vzorců

Tyto příklady vás naučí, jak Vlookup použít více kritérií, vrátit konkrétní instanci nebo všechny shody, provést dynamický Vlookup v více listů a další.

Je to druhá část série, která vám pomůže využít sílu aplikace Excel VLOOKUP. Příklady naznačují, že víte, jak tato funkce funguje. Pokud ne, má důvod začít se základním použitím VLOOKUP v aplikaci Excel.

Než se přesuneme dále, dovolte mi krátce vám připomenout syntaxi:

VLOOKUP (lookup_value, table_array, col_index_num,)

Nyní, když jsou všichni na stejné stránce, pojďme se blíže podívat na pokročilé příklady vzorců VLOOKUP:

  • Jak zobrazit Vlookup více kritérií v aplikaci Excel
  • Vlookup a návrat n-té shody
  • Vlookup více hodnot
  • Vlookup na základě hodnot řádků a sloupců
  • Vnořený vzorec Vlookup
  • VLOOKUP a INDIRECT k načítání dat z více listů

Jak vyhledávat více kritérií

Funkce Excel VLOOKUP je opravdu užitečná, pokud jde o vyhledávání v databázi pro určitou hodnotu. Chybí mu však důležitá vlastnost – jeho syntaxe umožňuje pouze jednu vyhledávací hodnotu. Ale co když chcete vyhledat několik podmínek? Můžete si vybrat z několika různých řešení.

Vzorec 1. VLOOKUP na základě tw o hodnoty

Předpokládejme, že máte seznam objednávek a chcete najít množství na základě 2 kritérií, jména zákazníka a produktu. Komplikujícím faktorem je, že každý zákazník si objednal více produktů, jak ukazuje následující tabulka:

Obvyklý vzorec VLOOKUP v tomto nebude fungovat situace, protože vrátí první nalezenou shodu na základě jediné vyhledávací hodnoty, kterou zadáte.

Chcete-li to překonat, můžete přidat sloupec pomocníka a zřetězit tam hodnoty ze dvou vyhledávacích sloupců (Zákazník a Produkt). Je důležité, aby sloupec pomocníka měl být v poli tabulky sloupcem úplně vlevo, protože v něm Excel VLOOKUP vždy hledá vyhledávací hodnotu.

Takže přidejte sloupec nalevo od tabulky a zkopírujte níže uvedený vzorec v tomto sloupci. Tím se sloupec pomocníka naplní hodnotami ze sloupců B a C (znak mezery je mezi nimi zřetězen pro lepší čitelnost):

=B2&" "&C2

A potom použijte standardní vzorec VLOOKUP a vložte obě kritéria do argumentu lookup_value oddělená mezerou:

=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)

Nebo zadejte kritéria do samostatných buněk (v našem případě G1 a G2) a zřetězte tyto buňky:

=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)

Jak jsme chcete vrátit hodnotu ze sloupce D, který je čtvrtý v poli tabulky, použijeme 4 pro col_index_num. Argument range_lookup je nastaven na FALSE, aby Vlookup odpovídal přesné shodě. Výsledek ukazuje následující snímek obrazovky:

Pokud je vyhledávací tabulka v jiném listu, zahrňte název listu do vzorce VLOOKUP. Například:

=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)

Alternativně vytvořte pojmenovaný rozsah pro vyhledávací tabulku (řekněme Objednávky), aby byl vzorec jednodušší – ke čtení:

=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)

Další informace najdete v části Jak Vlookup z jiného listu v aplikaci Excel.

Poznámka. Aby vzorec fungoval správně, měly by být hodnoty ve sloupci pomocníka zřetězeny přesně stejným způsobem jako v argumentu lookup_value. Například k oddělení kritérií v obou sloupcích pomocníka jsme použili znak mezery (B2 & „“ & C2) a vzorec VLOOKUP (G1 & „“ & G2).

Vzorec 2. Excel VLOOKUP s více podmínkami

Teoreticky můžete výše uvedený přístup k Vlookupu použít více než dvěma kritérii. H existuje však několik upozornění. Za prvé, vyhledávací hodnota je omezena na 255 znaků a za druhé, návrh listu nemusí umožňovat přidání pomocného sloupce.

Naštěstí Microsoft Excel často poskytuje více než jeden způsob, jak dělat totéž. Chcete-li Vlookup použít více kritérií, můžete použít kombinaci INDEX MATCH nebo funkci XLOOKUP, která byla nedávno zavedena v Office 365.

Chcete-li například vyhledat na základě 3 různých hodnot (datum, jméno zákazníka a produkt), použijte jeden z následujících vzorců:

=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))

=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)

Kde:

Poznámka: Ve všech verzích kromě aplikace Excel 365 by měl být INDEX MATCH zadán jako vzorec pole CSE stisknutí Ctrl + Shift + Enter. V aplikaci Excel 365, která podporuje dynamická pole, funguje také jako běžný vzorec.

Podrobné vysvětlení vzorců naleznete na stránce:

  • XLOOKUP s více kritérii
  • Vzorec INDEX MATCH s více kritérii

Jak používat VLOOKUP k získání 2., 3. nebo n-té shody

Jak již víte, Excel VLOOKUP může načíst pouze jednu odpovídající hodnotu, přesněji vrátí první nalezenou shodu. Ale co když je ve vašem vyhledávacím poli několik shod a chcete získat 2. nebo 3. instanci? Úkol zní docela složitě, ale řešení skutečně existuje!

Vzorec 1. Vlookup N-ta instance

Předpokládejme, že máte v jednom sloupci jména zákazníků, produkty, které zakoupili v jiném, a vy hledají 2. nebo 3. produkt zakoupený daným zákazníkem.

Nejjednodušší způsob je přidat sloupec pomocníka nalevo od tabulky, jako jsme to udělali v prvním příkladu. Tentokrát jej však naplníme jmény zákazníků a čísly výskytů, například „John Doe1“, „John Doe2“ atd.

Chcete-li výskyt získat, použijte funkci COUNTIF s odkazem na smíšený rozsah ( první reference je absolutní a druhá je relativní jako $ B $ 2: B2). Vzhledem k tomu, že se relativní reference mění na základě pozice buňky, kde je vzorec zkopírován, v řádku 3 se změní na $ B $ 2: B3, v řádku 4 – $ B $ 2: B4 atd.

Zřetězeno se jménem zákazníka (B2) má vzorec tuto podobu:

=B2&COUNTIF($B$2:B2, B2)

Výše uvedený vzorec jde do A2 a potom jej zkopírujte do tolika buněk, kolik je potřeba.

Poté zadejte název cíle a číslo výskytu do samostatných buněk (F1 a F2) a použijte následující vzorec k vyhledání konkrétního výskytu:

=VLOOKUP(F1&F2, A2:C11, 3, FALSE)

Vzorec 2. Vlookup 2. výskyt

Pokud hledáte 2. instanci vyhledávací hodnoty, můžete to udělat bez pomocného sloupce. Místo toho vytvořte pole tabulky dynamicky pomocí funkce INDIRECT společně s MATCH:

=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)

Kde:

  • E1 je vyhledávací hodnota
  • A2: A11 je vyhledávací rozsah
  • B11 je poslední (vpravo dole) buňka vyhledávací tabulky

Upozorňujeme, že výše uvedený vzorec je napsán pro konkrétní případ, kdy datové buňky ve vyhledávací tabulce začínají v řádku 2. Pokud je vaše tabulka někde v uprostřed listu použijte tento univerzální vzorec, kde A1 je levá horní buňka vyhledávací tabulky obsahující záhlaví sloupce:

=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)

Jak tento vzorec funguje

Zde je klíčová část vzorce, která vytváří dynamický rozsah vlookup:

INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")

Funkce MATCH nakonfigurovaná pro přesnou shodu (0 v posledním argumentu) porovná název cíle (E1) se seznamem jmen (A2: A11) a vrátí pozici první nalezené shody, což je 3 v o tvůj případ. Toto číslo bude použito jako počáteční souřadnice řádku pro rozsah vlookup, takže k němu přidáme 2 (+1 k vyloučení první instance a +1 k vyloučení řádku 1 s hlavičkami sloupců). Alternativně můžete použít 1 + ROW (A1) k automatickému výpočtu potřebné úpravy na základě polohy řádku záhlaví (v našem případě A1).

Ve výsledku získáme následující textový řetězec, který INDIRECT převádí na odkaz na rozsah:

INDIRECT("A"&5&":B11") -> A5:B11

Tento rozsah jde na argument table_array VLOOKUP, který jej nutí začít hledat v řádku 5, přičemž vynecháme první instanci vyhledávací hodnoty:

VLOOKUP(E1, A5:B11, 2, FALSE)

Jak Vlookup a vrátit více hodnot v aplikaci Excel

Funkce Excel VLOOKUP je navržena tak, aby vrátila pouze jednu shodu. Existuje způsob, jak Vlookup více instancí? Ano, existuje, i když není snadné. To vyžaduje kombinované použití několika funkcí, jako je INDEX, SMALL a ROW je maticový vzorec.

Například níže lze najít všechny výskyty vyhledávací hodnoty F2 v rozsahu vyhledávání B2: B16 a návrat více shod ze sloupce C:

{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}

Existují 2 způsoby, jak zadat vzorec v listu:

  1. Zadejte vzorec do první buňky, stiskněte Ctrl + Shift + Enter a poté jej přetáhněte dolů k několika dalším buňkám.
  2. Vyberte několik sousedních buněk v jednom sloupci (F1: F11 v snímek obrazovky níže), zadejte vzorec a dokončete jej stisknutím Ctrl + Shift + Enter.

Ať tak či onak, počet buněk, do kterých zadáte vzorec, by měl být stejný nebo větší než maximální počet možných shod.

Podrobné vysvětlení logiky vzorců a další příklady najdete v části VLOOKUP více hodnot v aplikaci Excel .

Jak Vlookup v řádcích a sloupcích (obousměrné vyhledávání)

Obousměrné vyhledávání (aka maticové vyhledávání nebo 2-dimenzionální vyhledávání) je fantastické slovo pro vyhledání hodnoty v průsečíku určitého řádku a sloupce.Existuje několik různých způsobů, jak provést dvourozměrné vyhledávání v aplikaci Excel, ale protože se tento výukový program zaměřuje na funkci VLOOKUP, přirozeně ji použijeme.

V tomto příkladu si vezmeme níže uvedená tabulka s měsíčním prodejem a vypracováním vzorce VLOOKUP k načtení prodejního čísla konkrétní položky v daném měsíci.

S názvy položek v A2: A9, názvy měsíců v B1: F1, cílová položka v I1 a cílovém měsíci v I2 má vzorec následující podobu:

=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)

Jak tento vzorec funguje

Jádrem vzorce je standardní funkce VLOOKUP, která hledá přesnou shodu s vyhledávací hodnotou v I1. Ale protože nevíme ve kterém přesně sloupci jsou tržby za konkrétní měsíc, nemůžeme zadat číslo sloupce přímo do argumentu col_index_num. K vyhledání tohoto sloupce použijeme následující funkci MATCH:

MATCH(I2, A1:F1, 0)

Přeloženo do angličtiny, vzorec říká: vyhledat hodnotu I2 v A1: F1 a vrátit její relativní polohu v poli. Zadáním 0 k 3. argumentu dáte příkazu MATCH, aby našel hodnotu přesně rovnou vyhledávací hodnotě (je to jako použití FALSE pro argument range_lookup VLOOKUP).

Protože Mar je ve 4. sloupci ve vyhledávacím poli vrátí funkce MATCH hodnotu 4, která přejde přímo na argument col_index_num VLOOKUP:

VLOOKUP(I1, A2:F9, 4, FALSE)

Věnujte pozornost že ačkoli názvy měsíců začínají ve sloupci B, používáme pro vyhledávací pole A1: I1. To se provádí, aby číslo vrácené MATCH odpovídalo pozici sloupce v table_array VLOOKUP.

Chcete-li se dozvědět více způsobů provedení maticového vyhledávání v aplikaci Excel, přečtěte si INDEX MATCH MATCH a další vzorce pro 2-dimenzionální vyhledávání.

Jak provést více Vlookup v aplikaci Excel (vnořený Vlookup)

Někdy se může stát, že vaše hlavní tabulka a vyhledávací tabulka nemají společný jediný sloupec, což vám znemožňuje provést Vlookup mezi dvěma tabulkami. Existuje však jiná tabulka, která neobsahuje informace, které hledáte, ale má jeden společný sloupec s hlavní tabulkou a další společný sloupec s vyhledávací tabulkou.

Níže uvedený obrázek ilustruje situaci:

Cílem je zkopírovat ceny do hlavní tabulky na základě ID položek. Problém je v tom, že tabulka obsahující ceny nemá ID položek, což znamená, že budeme muset udělat dva Vlookupy v jednom vzorci.

Kvůli pohodlí nejprve vytvořme několik pojmenovaných rozsahů :

  • Vyhledávací tabulka 1 má název Products (D3: E3).
  • Vyhledávací tabulka 2 má název Ceny (G3: H3).

Tabulky mohou být ve stejných nebo různých pracovních listech.

A nyní provedeme takzvaný dvojitý Vlookup, vnořený Vlookup.

Nejprve vytvořte VLOOKUP vzorec pro vyhledání názvu produktu ve vyhledávací tabulce 1 (s názvem Products) na základě ID položky (A3):

=VLOOKUP(A3, Products, 2, FALSE)

Dále vložte výše uvedený vzorec do argumentu lookup_value jiné funkce VLOOKUP a vytáhněte ceny z vyhledávací tabulky 2 (pojmenované Ceny) na základě názvu produktu vráceného vnořeným VLOOKUP:

=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)

Snímek obrazovky níže ukazuje náš vnořený vzorec Vlookup v akci:

Jak Vlookup více listy dynamicky

Někdy můžete mít data ve stejném formátu rozdělená do několika listů. A vaším cílem je získat data z konkrétního listu v závislosti na hodnotě klíče v dané buňce.

To může být snadněji pochopitelné z příkladu. Řekněme, že máte několik regionálních přehledů prodeje ve stejném formátu a hledáte získání údajů o prodeji konkrétního produktu v určitých oblastech:

Stejně jako v předchozím příkladu začneme definováním několika jmen:

  • Rozsah A2: B5 v listu CA má název CA_Sales.
  • Rozsah A2 : B5 v listu FL se jmenuje FL_Sales.
  • Rozsah A2: B5 v listu KS se jmenuje KS_Sales.

Jak vidíte, všechny pojmenované rozsahy mají společné část (prodej) a jedinečné součásti (CA, FL, KS). Nezapomeňte pojmenovat své rozsahy podobným způsobem, jaký je nezbytný pro vzorec, který budeme vytvářet.

Vzorec 1. INDIRECT VLOOKUP pro dynamické načítání dat z různých listů

Pokud je vaším úkolem načíst data z více listů, je nejlepším řešením vzorec VLOOKUP INDIRECT – kompaktní a snadno srozumitelný.

V tomto příkladu uspořádáme souhrnnou tabulku tímto způsobem:

  • Zadejte požadované produkty v A2 a A3. To jsou naše vyhledávací hodnoty.
  • Zadejte jedinečné části pojmenovaných rozsahů v B1, C1 a D1.

A teď zřetězíme buňku obsahující jedinečnou část (B1) se společnou částí („_Sales“) a výsledný řetězec přivedeme na INDIRECT:

INDIRECT(B$1&"_Sales")

Funkce INDIRECT transformuje řetězec na název, kterému Excel rozumí, a vložíte jej do argumentu table_array VLOOKUP:

=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)

Výše uvedený vzorec jde do B2 a poté jej zkopírujete dolů a doprava.

Věnujte prosím pozornost tomu, ve vyhledávací hodnotě ($ A2) jsme uzamkli souřadnici sloupce s absolutním odkazem na buňku, aby sloupec zůstal pevný, když se vzorec zkopíruje doprava. V odkazu B $ 1 jsme uzamkli řádek, protože chceme sloupec souřadnice pro změnu a dodání příslušné části názvu NEPŘÍMÉ v závislosti na sloupci, do kterého je vzorec zkopírován:

Pokud je vaše hlavní tabulka uspořádána jinak, vyhledávací hodnoty v řádku a jedinečné části názvů rozsahu v colu mn, pak byste měli zamknout souřadnici řádku ve vyhledávací hodnotě (B $ 1) a souřadnici sloupce v částech názvu ($ A2):

=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)

Vzorec 2. VLOOKUP a vnořené IF pro vyhledání více listů

V situaci, kdy máte právě dva nebo tři vyhledávací listy, můžete použít poměrně jednoduchý vzorec VLOOKUP s vnořenými funkcemi IF a vybrat správný list na základě hodnoty klíče v konkrétní buňce:

=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)

Kde $ A2 je vyhledávací hodnota (název položky) a B $ 1 je klíčová hodnota (stát):

V tomto případě nemusíte nutně definovat názvy a můžete použít externí odkazy k odkazování na jiný list nebo sešit.

Další příklady vzorců najdete v části Jak SVYHLEDAT na více listů v aplikaci Excel .

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

Praktický sešit ke stažení

Pokročilé příklady vzorců VLOOKUP (soubor .xlsx)

  • Výukový program Excel VLOOKUP pro začátečníky
  • Jak zobrazit více shody v aplikaci Excel
  • Jak používat VLOOKUP & Funkce SUM nebo SUMIF v Excel
  • INDEX & MATCH funkce v aplikaci Excel – lepší alternativa k VLOOKUP
  • 4 způsoby, jak rozlišovat velká a malá písmena Vlookup v aplikaci Excel
  • VLOOKUP aplikace Excel nefunguje – chyby N / A, NÁZEV a HODNOTA

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *