VLOOKUP er en stærk funktion. Men jeg får ofte et spørgsmål i et af mine Power Excel-seminarer fra nogen, der ønsker at vide, om VLOOKUP kan returnere alle matchende værdier. Som du ved, vil VLOOKUP med False som det fjerde argument altid returnere den første kamp, den finder. I det følgende skærmbillede returnerer celle F2 3623, fordi det er det første match, der blev fundet for job J1199.
Spørgsmålet kan så VLOOKUP returnere alle kampene?
VLOOKUP vil ikke. Men andre funktioner kan.
Hvis du vil sammenholde alle omkostningerne fra job J1199, bruger du =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Hvis du har tekstværdier og vil forbinde alle resultaterne til en enkelt værdi, kan du bruge =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Denne formel fungerer kun i Office 365 og Excel 2019.
Eller du skal muligvis returnere alle resultaterne for et enkelt job i et nyt område på regnearket. En helt ny =FILTER(B2:C53,A2:A53=K1,"None Found")funktion, der kommer til Office 365 i 2019, løser problemet:
Nogle gange vil folk udføre alle VLOOKUP'erne og sammenfatte dem. Hvis din opslagstabel er sorteret, kan du bruge den =SUM(LOOKUP(B2:B53,M3:N5)).
Hvis du har brug for at sammenfatte alle VLOOKUP'er med Exact Match-versionen af VLOOKUP, skal du have adgang til dynamiske arrays for at bruge den =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Hvis du vil lære mere om dynamiske arrays, skal du tjekke Excel Dynamic Arrays Straight To The Point.
Se video
Videoudskrift
Lær Excel fra, Podcast-afsnit 2247: Kan du returnere alle VLookUp-værdier?
Hej. Velkommen tilbage til netcast. Jeg er Bill Jelen. To spørgsmål kom op på mit seminar i Appleton, Wisconsin i sidste uge - begge relaterede. De sagde, hej, hvordan returnerer vi alle VLOOKUP'er, okay? I dette tilfælde, ligesom J1199 har en masse tændstikker, og de ved du, vil returnere dem alle, og mit første spørgsmål, når nogen spørger mig, er, ja, hvad vil du gøre med tændstikkerne? Er det tal, du vil tilføje, eller er det tekst, du vil sammenkæde? Og det er sjovt. De to spørgsmål i det samme seminar, den ene person ville tilføje dem, og den anden person ville sammenkæde resultaterne.
Så lad os se på begge disse. Tjek ned i YouTube-beskrivelsen for en indholdsfortegnelse, hvor du kan springe til den anden, hvis du vil se resultatet af teksten.
Okay, så første ting, hvis vi vil tilføje dem alle sammen, vil vi slet ikke bruge en VLOOKUP. Vi skal bruge en funktion kaldet SUMIF eller SUMIFS, som vil opsummere alt, der matcher denne vare. Så SUMIFS. Her er de numeriske værdier, som vi vil sammenfatte, og jeg trykker på F4 for at låse det. På den måde, når jeg kopierer dette ned, vil det fortsætte med at pege på det samme interval, og så vil vi gå og kontrollere, om JOB-nummeret i kolonne A, igen F4 der, er = til værdien til venstre for os - i dette tilfælde E2 - og når vi kopierer det ned, vil vi se TOTAL for hvert emne. (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Lad os bare tjekke lidt her. J1199. Det samlede beløb er 25365. Okay. Så det fungerer. Hvis det er tal, og du vil hente alle numrene og tilføje dem, skal du skifte til SUMIF eller SUMIFS, men hvis det er tekst, okay, nu er denne funktion ny i Office 365 i februar 2017. Så hvis du har Excel 2016 eller Excel 2013 eller Excel 2010 eller nogen af de ældre, har du ikke denne funktion. Det er en funktion kaldet TEXTJOIN. TEXTJOIN. Dette er en anden funktion fra (Joe McDade - 01:50), der lige bragte os alle de store dynamiske matrixformler på Ignite i 2018, og Joe sørgede for, at TEXTJOIN ville arbejde med arrays, hvilket er virkelig godt.
Så afgrænsningen her vil være, RUM, ignorere TOM bestemt. Vi vil ignorere TOM her, fordi vi genererer en masse tomheder i denne næste del, IF-udsagnet. HVIS det element over en A2, F4, er = til dette JOB-nummer her, så vil jeg have det tilsvarende element fra kolonne C, F4, ellers vil jeg have "" sådan. Luk IF-erklæringen. Luk TEXTJOIN. Skal jeg trykke på CONTROL + SHIFT + ENTER? Nej jeg gør ikke. Det bringer mig alle de produkter, der matcher sådan, okay? Så hvis vi returnerer alle VLOOKUP'er, hvis vi vil sammenfatte dem, ja, hvis vi vil sammenkæde dem, ja. (= TEXTJOIN (“,”, True, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, "")))
Okay, nu er der en anden mulighed her, når folk spørger mig, om de kan returnere alle VLOOKUP'er. Det kan være et problem, hvor vi vil slå op på hver af disse omkostninger her og finde ud af HÅNDTERINGSKOSTNADEN og derefter opsummere dem alle. Ligesom, jeg ønsker ikke at sætte en VLOOKUP her og en VLOOKUP her og en VLOOKUP her og en VLOOKUP her. Jeg vil bare gøre dem helt, og i så fald skal vi bruge SUM-funktionen og derefter den gamle, gamle LOOKUP-funktion. LOOKUP siger, at vi skal slå alle disse værdier op i kolonne B. Jeg har ikke brug for F4 her, fordi jeg ikke kopierer det nogen steder. ,. Her er vores opslagstabel. ), luk SUMMET, og det slukkes og gør hver enkelt VLOOKUP og derefter summer dem alle sådan. (= SUM (LOOKUP (B2: B53, K3: L5)))
Nå, hej. Alle disse emner er min bog LIV: De 54 største tip nogensinde. Klik på det i øverste højre hjørne for at lære mere.
Så spørgsmålet er, kan du returnere alle VLOOKUP'er? Nå, slags, men bruger faktisk ikke VLOOKUP. Vi skal enten bruge SUMIF, TEXTJOIN eller SUM eller LOOKUP til at løse det.
Nå, hej. Jeg vil gerne takke dig, fordi du kom forbi. Vi ses næste gang til endnu en netcast fra.
Du ved, okay, jeg har talt om disse dynamiske arrays i en uge. Jeg ville lave en video, hvor jeg ikke rørte ved dynamiske arrays, fordi jeg ved, at mange mennesker ikke har dem endnu, men her er vi. Det er udtaget. Du ved, disse er ikke alfabetiske. Dette ville være så meget bedre, hvis vi kunne sortere dem, og hvis du tilfældigvis har de nye dynamiske arrays, kan du sende dette til SORT-funktionen, SORTERE sådan og trykke ENTER, og nu vil resultaterne blive sorteret sådan.
Du ved, selv denne formel kan blive bedre med de dynamiske arrays. Opslaget kræver, at du bruger SAND. Hvad hvis du ville bruge en, FALSE? Vi kunne ændre det til en VLOOKUP, slå hele denne tekst op i den tabel, 2,. I dette tilfælde vil jeg bruge SAND, men i et andet tilfælde kan du bruge FALSE. KONTROL + SKIFT + ENTER. Nej. Det kommer bare til at fungere, okay? (= SUM (VLOOKUP (B2: B53, K3: L5,2, True)))
Dynamiske arrays, der udkommer i begyndelsen af 2019, løser så mange problemer.
Tak for at hænge ud gennem udtaget her. Vi ses næste gang til endnu en netcast fra.
Download Excel-fil
For at downloade excel-filen: can-you-return-all-vlookup-values.xlsx
Når nogen spørger "Kan VLOOKUP returnere alle tændstikkerne, er svaret nej. Men der er mange andre funktioner, der stort set kan gøre det samme.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke at tænke over:
"Normaliser dine data, som du ville have andre til at normalisere deres data for dig"
Kevin Lehrbass
