VLOOKUP är en kraftfull funktion. Men jag får ofta en fråga i ett av mina Power Excel-seminarier från någon som vill veta om VLOOKUP kan returnera alla matchande värden. Som du vet kommer VLOOKUP med False som det fjärde argumentet alltid att returnera den första matchen som den hittar. I följande skärmdump returnerar cell F2 3623 eftersom det är den första matchningen som hittades för jobbet J1199.
Frågan kan då VLOOKUP returnera alla matcher?
VLOOKUP kommer inte. Men andra funktioner kan.
Om du vill totala alla kostnader från jobb J1199, skulle du använda =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Om du har textvärden och vill sammanfoga alla resultaten till ett enda värde kan du använda =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Den här formeln fungerar bara i Office 365 och Excel 2019.
Eller du kan behöva returnera alla resultat för ett enda jobb till ett nytt område i kalkylbladet. En helt ny =FILTER(B2:C53,A2:A53=K1,"None Found")funktion som kommer till Office 365 2019 kommer att lösa problemet:
Ibland vill folk utföra alla VLOOKUPs och summera dem. Om din uppslagstabell är sorterad kan du använda den =SUM(LOOKUP(B2:B53,M3:N5)).
Om du behöver summera alla VLOOKUP med Exakt Match-versionen av VLOOKUP, måste du ha tillgång till Dynamic Arrays för att kunna använda den =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
För att lära dig mer om dynamiska matriser, kolla in Dynamiska matriser i Excel direkt till punkten.
Kolla på video
Videoutskrift
Lär dig Excel från, Podcast avsnitt 2247: Kan du returnera alla VLookUp-värden?
Hallå. Välkommen tillbaka till netcast. Jag heter Bill Jelen. Två frågor kom upp i mitt seminarium i Appleton, Wisconsin förra veckan - båda relaterade. De sa, hej, hur returnerar vi alla VLOOKUPs, okej? I det här fallet, som om J1199 har en massa tändstickor och de, du vet, vill returnera dem alla, och min första fråga när någon frågar mig detta är, ja, vad vill du göra med tändstickorna? Är det siffror som du vill lägga till eller är det text som du vill sammanfoga? Och det är roligt. De två frågorna i samma seminarium, en person ville lägga till dem och den andra personen ville sammanfoga resultaten.
Så låt oss ta en titt på båda dessa. Kolla ner i YouTube-beskrivningen för en innehållsförteckning där du kan hoppa till den andra om du vill se resultatet av texten.
Okej, så först, om vi vill lägga till dem alla kommer vi inte att använda en VLOOKUP alls. Vi ska använda en funktion som heter SUMIF eller SUMIFS som kommer att summera allt som matchar det här föremålet. Så, SUMIFS. Här är de numeriska värdena som vi vill summera och jag trycker på F4 för att låsa det. När jag kopierar ner det kommer det att fortsätta att peka på samma intervall, och sedan vill vi kolla och se om JOB-numret i kolumn A, återigen F4 där, är = till värdet till vänster om oss - i det här fallet E2 - och när vi kopierar ner det ser vi TOTALT för varje objekt. (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Låt oss bara göra en liten kontroll här. J1199. Totalt är 25365. Okej. Så det fungerar. Om det är siffror och du vill få alla siffror och lägga till dem, byt till SUMIF eller SUMIFS, men om det är text, okej, nu är den här funktionen ny i Office 365 i februari 2017. Så om du har Excel 2016 eller Excel 2013 eller Excel 2010 eller någon av de äldre, du kommer inte att ha den här funktionen. Det är en funktion som heter TEXTJOIN. TEXTJOIN. Det här är en annan funktion från (Joe McDade - 01:50) som bara gav oss alla de fantastiska dynamiska matrisformlerna på Ignite 2018, och Joe såg till att TEXTJOIN skulle fungera med arrays, vilket är riktigt bra.
Så avgränsaren här kommer att bli, RYMD, ignorera TOMT definitivt. Vi vill ignorera TOM här eftersom vi kommer att generera en hel del tömningar i den här nästa delen, IF-uttalandet. OM det objektet över en A2, F4, är = till detta JOB-nummer här, så vill jag ha motsvarande objekt från kolumn C, F4, annars vill jag "" så. Stäng det IF-uttalandet. Stäng TEXTJOIN. Måste jag trycka på CONTROL + SHIFT + ENTER? Nej det gör jag inte. Det ger mig alla produkter som matchar så, okej? Så, att returnera alla VLOOKUPs, om vi vill summera dem, ja, om vi vill sammanfoga dem, ja. (= TEXTJOIN (“,”, True, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, “”)))
Okej, nu finns det en annan möjlighet här när folk frågar mig om de kan returnera alla VLOOKUP. Det kan vara en fråga där vi vill slå upp var och en av dessa kostnader här och ta reda på hanteringskostnaden och sedan summera dem alla. Jag vill inte sätta en VLOOKUP här och en VLOOKUP här och en VLOOKUP här och en VLOOKUP här. Jag vill bara göra dem helt och i så fall ska vi använda SUM-funktionen och sedan den gamla, gamla LOOKUP-funktionen. LOOKUP säger att vi ska slå upp alla dessa värden i kolumn B. Jag behöver inte F4 här eftersom jag inte kopierar den någonstans. ,. Här är vår uppslagstabell. ), stäng SUMMET, och det slocknar och gör varje individ VLOOKUP och summerar dem alla så. (= SUMMA (SÖKNING (B2: B53, K3: L5)))
Men Hej. Alla dessa ämnen är min bok LIV: De 54 bästa tipsen genom tiderna. Klicka på det i det övre högra hörnet för att lära dig mer.
Så frågan är att du kan returnera alla VLOOKUPs? Tja, typ av, men egentligen inte använder VLOOKUP. Vi ska antingen använda SUMIF, TEXTJOIN eller SUM eller LOOKUP för att lösa det.
Men Hej. Jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Du vet, okej, jag har pratat om dessa dynamiska matriser i en vecka. Jag ville göra en video där jag inte rörde dynamiska matriser eftersom jag vet att många inte har dem ännu, men här är vi. Det är uttaget. Du vet, dessa är inte alfabetiska. Detta skulle vara så mycket bättre om vi kunde sortera dem, och om du råkar ha de nya dynamiska matriserna, kan du skicka detta till SORT-funktionen, SORTERA så här och tryck på ENTER, och nu kommer resultaten att sorteras så.
Du vet, även denna formel kan bli bättre med de dynamiska matriserna. Sökningen kräver att du använder, SANT. Vad händer om du vill använda en, FALSE? Vi kan ändra det till en VLOOKUP, slå upp all denna text i den tabellen, 2,. I det här fallet ska jag använda SANT men i ett annat fall kan du använda FALSE. KONTROLL + SKIFT + RETUR. Nej. Det kommer bara att fungera, okej? (= SUMMA (VLOOKUP (B2: B53, K3: L5,2, True)))
Dynamiska arrays som kommer ut i början av 2019 kommer att lösa så många problem.
Tack för att du umgås genom uttaget här. Vi ses nästa gång för en ny netcast från.
Ladda ner Excel-fil
För att ladda ner excel-filen: can-you-return-all-vlookup-values.xlsx
När någon frågar "Kan VLOOKUP returnera alla matcher, är svaret nej. Men det finns många andra funktioner som kan göra i princip samma sak.
Excel-tanke på dagen
Jag har frågat mina Excel Master-vänner om deras råd om Excel. Dagens tanke att fundera på:
"Normalisera dina data som du skulle ha andra normalisera deras data åt dig"
Kevin Lehrbass
