VLOOKUP k vrácení více hodnot | Průvodce krok za krokem

Excel Vlookup pro vrácení více hodnot

Jednou z klíčových funkcí funkce VLOOKUP je, že bude pracovat pro jedinečné hodnoty a pokud existují nějaké duplicitní hodnoty, vrátí se jakákoli první nalezená hodnota i pro všechny ostatní vyhledávací hodnoty. Toto je jedna z klíčových věcí, které musíme mít na paměti při používání vzorce VLOOKUP. Když se vyhledávací hodnota objeví vícekrát a pokud má více hodnot, musíme zahrnout různé strategie, v tomto článku vám ukážeme, jak vrátit více hodnot pomocí funkce VLOOKUP.

Jak vrátit více hodnot pomocí funkce Vlookup?

Jak jsme řekli výše, VLOOKUP funguje pro jedinečné hodnoty a pro duplicitní hodnoty vrátí první nalezenou hodnotu.

Tuto šablonu Vlookup pro vrácení více hodnot Excel si můžete stáhnout zde - Vlookup pro vrácení více hodnot Excel šablony

Podívejte se například na níže uvedená data.

Máme „tabulku 1“ a „tabulku 2“, v „tabulce 1“ máme ovoce a jejich ceny v různých městech a pro „tabulku 2“ musíme zjistit nákladovou cenu z „tabulky 1“ pomocí funkce VLOOKUP. Nejprve použijte funkci VLOOKUP.

  • Podívejte se, máme stejnou cenu pro všechna města. Například v „tabulce 1“ pro „Apple“ ve městě „Bangalore“ máme 108, protože se jedná o první hodnotu nalezenou v tabulce pro „Apple“, která pro všechny města vrátila stejnou hodnotu.
  • Podobně pro „Grapes“ je první hodnota 79 a stejná byla vrácena pro všechna města a v případě „Orange“ také vrátila 56 pro všechna města.

V těchto případech tedy musíme vytvořit pomocný sloupec a vytvořit tak jedinečný seznam hodnot vyhledávání. Každé ovoce má pro každé město jiné ceny, takže kombinace názvu ovoce a města může vytvořit jedinečný seznam, vložit sloupec pomocníka a kombinovat název ovoce a název města.

Takže každý název ovoce v kombinaci s městem včetně zpětného lomítka (/) jako oddělovače mezi názvem ovoce a názvem města.

Nyní se vraťte k „tabulce 2“ a otevřete funkci VLOOKUP.

Nyní musíme zahrnout stejnou strategii pomocného sloupce, abychom mohli vybrat vyhledávací hodnotu, nejprve zvolit název ovoce.

Poté zkombinujte zpětné lomítko a poté jej zkombinujte s názvem města.

Nyní zkombinujte název města.

Nyní je vyhledávací hodnota podobná sloupci pomocníka, nyní vyberte pole tabulky počínaje sloupcem pomocníka.

Nyní uveďte číslo sloupce jako 4 a vyhledávání rozsahu jako FALSE nebo 0.

Tady máš, máme nový ceník nákladů s přesnými čísly, řekněme díky pomocnému sloupci nebo kombinaci Fruit Name & City.

Pro více hodnot použijte alternativní metody

Viděli jsme, jak může být pomocný sloupec užitečný k načtení více hodnot pomocí vzorce VLOOKUP. Ale představte si situaci níže.

V tomto nemáme žádný název města k vytvoření sloupce zřetězení, takže možná budeme muset použít různé strategie níže je složitý vzorec, který můžeme použít k získání více hodnot duplicitních jedinečných hodnot.

= INDEX ($ B $ 2: $ B $ 11, MALÉ (POKUD (E3 = $ A $ 2: $ A $ 11, ŘÁDEK ($ A $ 2: $ A $ 11)) - ŘÁDEK ($ A $ 2) +1), ŘÁDEK (1: 1)))

Poznámka: Výše uvedený vzorec je maticový vzorec, takže je třeba jej zavřít pomocí Ctrl + Shift + Enter .

Tento vzorec vypadá zdlouhavě, že? Máme však jinou alternativní metodu, tj. Kombinovat název ovoce s jejich počtem v seznamu.

Použijte níže uvedenou funkci COUNTIF k vytvoření pomocného sloupce.

Výše uvedená funkce nám poskytne počet každého ovoce v kombinaci se samotným názvem ovoce. Pro příklad se podívejte na řádek číslo 4 v tomto, máme počet „Apple“ dvakrát, takže count říká 2 a v kombinaci s názvem ovoce nám dává „2Apple“. Tak se vytvoří jedinečný seznam ovoce.

Nyní vytvořte vyhledávací tabulku jako níže.

Nyní otevřete funkci VLOOKUP ve vyhledávací tabulce, tj. V buňce H3.

Ve sloupci pomocníka se počítá první kombinovaná hodnota, takže zde vyberte číselnou hodnotu a poté zkombinujte s názvem ovoce.

Nyní vyberte tabulku a zadejte indexové číslo sloupce, abyste získali výsledek.

Věci k zapamatování

  • VLOOKUP vrátí stejnou hodnotu pro vyhledávací hodnoty, pokud má vyhledávací hodnota duplicitní názvy.
  • Chcete-li načíst více hodnot stejné vyhledávací hodnoty, musíme vytvořit pomocné sloupce pomocí kterékoli z výše uvedených 3 metod.