Ve funkci VLOOKUP často definujeme col_index_no static. Pevně to zakódujeme do vzorce VLOOKUP, jako VLOOKUP (id, data,3, 0). Problém nastává, když vložíme nebo odstraníme sloupec v datech. Pokud odstraníme nebo přidáme sloupec před nebo za třetí sloupec, třetí sloupec již nebude odkazovat na zamýšlený sloupec. To je jeden problém. Jiné je, když máte více sloupců k vyhledávání. V každém vzorci budete muset upravit index sloupců. Jednoduché kopírování nepomůže.
Ale co když můžete říct VLOOKUP, aby se podíval na nadpisy a vrátil pouze odpovídající hodnotu nadpisů. Tomu se říká obousměrné VLOOKUP.
Mám -li například vzorec VLOOKUP proznačky sloupec, pak by měl hledat VLOOKUP značky sloupec v datech a návratová hodnota z tohoto sloupce. Tím se náš problém vyřeší.
Hmm … Dobře, jak to tedy uděláme? Použitím funkce Match v rámci funkce VLOOKUP.
Obecný vzorec
=VLOOKUP(lookup_value, table_array, MATCH (lookup_heading, table_headings, 0), 0)
Lookup_value: vyhledávací hodnota v prvním sloupci table_array.
Tabulka_pole: rozsah, ve kterém chcete provést vyhledávání. Např. A2, D10.
Lookup_heading: nadpis, který chcete vyhledat v nadpisech table_array.
Nadpisy tabulek: Odkaz na nadpisy v poli tabulky. Např. pokud je tabulka A2, D10 a nadpisy v horní části každého sloupce, pak jeho A1: D1.
Takže teď víme, co potřebujeme pro dynamický col_index, pojďme si vše vyjasnit na příkladu.
Příklad dynamického VLOOKUPU
Pro tento příklad máme tuto tabulku, která obsahuje data studentů v rozsahu A4: E16.
Pomocí roll no a záhlaví chci načíst data z této tabulky. V tomto případě chci v buňce H4 získat data o roli, která není zapsána v buňce G4, a o záhlaví v H3. Pokud změním nadpis, data z příslušného rozsahu by měla být získána v buňce H4.
Napište tento vzorec do buňky H4
= VLOOKUP (G4, B4: E16, MATCH (H3, B3: E3,0), 0)
Protože naše tabulkové pole je B4: E16, naše pole nadpisů se změní na B3: E3.
Poznámka: Pokud jsou vaše data dobře strukturovaná, záhlaví sloupců bude mít stejný počet sloupců a bude to první řádek v tabulce.
Jak to funguje:
Hlavní částí je tedy automatické vyhodnocení indexového čísla sloupce. K tomu jsme použili funkci MATCH.
ZÁPAS (H3, B3: E3,0): Protože H3 obsahuje „student“, MATCH vrátí 2. Pokud by H3 měl „Grade“, vrátil by 4 atd. Vzorec VLOOKUP bude mít konečně col_index_num.
= VLOOKUP (G4, B4: E16,2,0)
Jak víme, funkce MATCH vrací indexové číslo dané hodnoty v zadaném jednorozměrném rozsahu. Proto MATCH vyhledá jakoukoli hodnotu zapsanou v H3 v rozsahu B3: E3 a vrátí její indexové číslo.
Nyní, kdykoli změníte nadpis v H3, pokud je v nadpisech, tento vzorec vrátí hodnotu z příslušného sloupce. V opačném případě se zobrazí chyba #N/A.
VLOOKUP ve více sloupcích rychle
Ve výše uvedeném příkladu jsme potřebovali odpověď z jedné hodnoty sloupce. Ale co když chcete získat více sloupců najednou. Pokud zkopírujete výše uvedený vzorec, vrátí chyby. Musíme v něm udělat nějaké drobné změny, aby byl přenosný.
Použití absolutních referencí s VLOOKUP
Do buňky H2 napište níže vzorec.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, MATCH (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Nyní zkopírujte H2 do všech buněk v rozsahu H2: J6, abyste jej vyplnili daty.
Jak to funguje:
Tady jsem dal absolutní reference každého rozsahu kromě řádku ve vyhledávací hodnotě pro VLOOKUP ($ G2) a sloupec v lookup_value pro MATCH (1 H $).
$ G2: To umožní, aby se řádek změnil na vyhledávací hodnotu pro funkci VLOOKUP při kopírování dolů, ale omezil změnu sloupce při kopírování doprava. Díky tomu bude VLOOKUP hledat Id ze sloupce G pouze s relativním řádkem.
Podobně, 1 H $ umožní změnit sloupec při kopírování vodorovně a omezit řádek při kopírování směrem dolů.
Použití pojmenovaných rozsahů
Výše uvedený příklad funguje dobře, ale je náročné číst a psát tento vzorec. A to není vůbec přenosné. To lze zjednodušit pomocí pojmenované rozsahy.
Zde nejprve uděláme nějaké pojmenování. Pro tento příklad jsem pojmenoval
$ B $ 2: $ E $ 14: jako Data
$ B $ 1: $ E $ 1: jako nadpisy
1 H $: Pojmenujte to jako Nadpis. Udělejte sloupce relativní. Chcete -li to provést, vyberte H1. Stiskněte CTRL+F3, klikněte na nové, v části Odkazy odstraňte '$' z přední části H.
$ G2: Podobně to pojmenujte jako RollNo. Tentokrát je řádek relativní odstraněním '$' z přední části 2.
Nyní, když máte všechna jména na listu, napište tento vzorec kamkoli do souboru aplikace Excel. Vždy dostane správnou odpověď.
= VLOOKUP (RollNo, Data, MATCH (nadpis, nadpisy, 0), 0)
Víte, každý si to může přečíst a porozumět tomu.
Pomocí těchto metod tedy můžete col_index_num udělat dynamickým. Dejte mi vědět, jestli to bylo užitečné v sekci komentáře níže.
Jak používat tFunkce VLOOKUP v Excelu
Relativní a absolutní reference v aplikaci Excel
Pojmenované rozsahy v aplikaci Excel
Jak VYHLEDAT z jiného listu aplikace Excel
VLOOKUP Více hodnot
Populární články
50 Zkratka aplikace Excel pro zvýšení produktivity : Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat tFunkce VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.