Je možné použiť VLOOKUP na vypísanie viacerých hodnôt?

Potrebujem pomôcť s funkciou VLOOKUP. V mojom súbore sú 2 tabuľky na dvoch rozdielnych hárkoch. Potrebujem, aby mi do hárku Table1 stĺpca Lists vypísalo, všetky nájdené dostupné farby zo stlpca Colors podľa kódu Ref. VLOOKUP mi ale vypíše len jedno. Ja potrebujem, aby vypísalo všetky dostupné farby oddelené čiarkou, napr. ku kódu AX2234 by malo vypísať red, blue, white.

Filip

Pomocou funkcie VLOOKUP sa dané údaje nepodarí získať. Tým ale nechcem povedať, že to nie je možné! 🙂

Ukážeme si 2 spôsoby:

• využitie maticových vzorcov a funkcií TEXTJOIN a IF (nefunguje vždy a všade);
• vytvorenie vlastnej funkcie (funguje výborne).

Využitie maticových vzorcov

Najskôr je potrebné povedať, že funkcia TEXTJOIN je podporovaná až od verzie Office 2016 inštalovanej prostredníctvom balíka Office 365. V bežnom Exceli 2016 ju nenájdete.

Máme k dispozícii hárky Table1 a Table2.

Do bunky C2 napíšeme nasledovný vzorec:

=TEXTJOIN(„, „;TRUE;IF(Table2!A$2:A$73=Table1!A2;Table2!B$2:B$73;““))

Stlačíme CTRL+SHIFT+ENTER. V bunke by mal byť následne vzorec automaticky uzavretý v zátvorkách {}.

{=TEXTJOIN(„, „;TRUE;IF(Table2!A$2:A$73=Table1!A2;Table2!B$2:B$73;““))}

Funkcia TEXTJOIN má 3 argumenty:

• oddeľovač – v našom prípade čiarka;
• TRUE/FALSE – či sa majú ignorovať prázdne bunky;
• bunky alebo rozsahy, ktoré sa majú spájať.

Prvé 2 argumenty sú jasné.

V 3. argumente funkcia IF overuje, či niektorá z buniek v rozsahu Table2!A$2:A$73 je zhodná s údajom v bunke Table1!A2. Ak áno, tak vypíše príslušnú hodnotu z daného riadka rozsahu Table2!B$2:B$73. Ak nie, nevypíše nič.

Samozrejme, aby bolo možné vzorec kopírovať a vyhodnocovať všetky údaje, je potrebné správne použiť absolútne a relatívne adresovanie.

Funkcia IF postupne vyhodnocuje porovnanie údajov z Table2 s údajom v bunke A2 – AX2234:

(true; true; true; false; ………………….)

Pri hodnote TRUE získame hodnotu zo stĺpca B, pri hodnotu FALSE „“. Výsledkom je pole:

(„red“; „blue“; „white“; „“; …………………..)

Toto pole sa berie ako 3. argument funkcie TEXTJOIN, ktorá údaje pomocou čiarky spojí a prázdne reťazce odignoruje.

Vytvorenie vlastnej funkcie

Tento spôsob sa mi páči viac 🙂 a funguje naprieč rôznymi verziami Excelu.

Vytvoríme si vlastnú funkciu prostredníctvom VBA (ALT+F11) so 4 argumentmi … hladajaspoj(Vyhladavana_hodnota, Hladaj_v_stlpci, Vysledky_zo_stlpca, Oddelovac).

Ospravedlňujem sa za používanie slovenčiny v kóde :).

Function hladajaspoj(Vyhladavana_hodnota As String, Hladaj_v_stlpci As Range, Vysledky_zo_stlpca As Range, Oddelovac As String)
    
    Dim i As Long
    Dim result As String

    For i = 1 To Hladaj_v_stlpci.Count
        If Hladaj_v_stlpci.Cells(i, 1) = Vyhladavana_hodnota Then
            If result = "" Then
                result = Vysledky_zo_stlpca.Cells(i, 1).Value
            Else
                result = result & Oddelovac & Vysledky_zo_stlpca.Cells(i, 1).Value
            End If
        End If
        Next

    hladajaspoj = Trim(result)

End Function

Jednoduché!

Funkciu použijeme rovnako, ako každú inú. Do bunky B2 zadáme =hladajaspoj(A2;Table2!A$2:A$73;Table2!B$2:B$73;“, „), stlačíme Enter a vzorec skopírujeme. Výsledok…

Vo funkcii používame cyklus For Next, ktorý slúži na opakovanie príkazov vo vnútri cyklu pre všetky bunky v rozsahu Table2!A$2:A$73.

Vo vnútri cyklu prostredníctvom IF THEN ELSE vyhodnocujeme, či sa vyhľadávaná hodnota (Vyhladavana_hodnota) zhoduje s hodnotou v stĺpci A v Table2 (Hladaj_v_stlpci). Ak áno, tak hodnota zo stĺpca B (Vysledky_zo_stlpca) sa pripojí k aktuálnej hodnote premennej result.

Samozrejme pripájam aj súbor na stiahnutie.

Verím, že som pomohol. 🙂 Ak máte ďalšie otázky, pokojne píšte!