VLOOKUP er en kraftig funksjon. Men jeg får ofte et spørsmål i et av Power Excel-seminarene mine fra noen som vil vite om VLOOKUP kan returnere alle samsvarende verdier. Som du vet, vil VLOOKUP med False som fjerde argument alltid returnere den første kampen den finner. I det følgende skjermbildet returnerer celle F2 3623 fordi det er den første kampen som ble funnet for jobben J1199.
Spørsmålet kan da VLOOKUP returnere alle kampene?
VLOOKUP vil ikke. Men andre funksjoner kan.
Hvis du vil beregne alle kostnadene fra jobb J1199, vil du bruke =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Hvis du har tekstverdier og vil koble alle resultatene til en enkelt verdi, kan du bruke =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Denne formelen fungerer bare i Office 365 og Excel 2019.
Eller du må kanskje returnere alle resultatene for en enkelt jobb i et nytt område av regnearket. En helt ny =FILTER(B2:C53,A2:A53=K1,"None Found")funksjon som kommer til Office 365 i 2019 vil løse problemet:
Noen ganger vil folk utføre alle VLOOKUP-ene og oppsummere dem. Hvis oppslagstabellen din er sortert, kan du bruke den =SUM(LOOKUP(B2:B53,M3:N5)).
Hvis du trenger å summere alle VLOOKUP-er med Exact Match-versjonen av VLOOKUP, må du ha tilgang til Dynamic Arrays for å kunne bruke den =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
For å lære mer om dynamiske matriser, sjekk ut Excel dynamiske matriser rett til punktet.
Se på video
Videoutskrift
Lær Excel fra, Podcast Episode 2247: Kan du returnere alle VLookUp-verdier?
Hei. Velkommen tilbake til netcast. Jeg er Bill Jelen. To spørsmål kom opp i seminaret mitt i Appleton, Wisconsin forrige uke - begge relatert. De sa, hei, hvordan returnerer vi alle VLOOKUP-er, ok? I dette tilfellet, som J1199, har en haug med fyrstikker, og de, vet du, vil returnere dem alle, og mitt første spørsmål når noen spør meg om dette er, vel, hva vil du gjøre med fyrstikkene? Er det tall du vil legge sammen, eller er det tekst du vil sammenkoble? Og det er morsomt. De to spørsmålene i samme seminar, den ene personen ønsket å legge dem sammen, og den andre personen ønsket å sammenkoble resultatene.
Så la oss ta en titt på begge disse. Sjekk ned i YouTube-beskrivelsen for en innholdsfortegnelse der du kan hoppe til den andre hvis du vil se resultatet av teksten.
Ok, så først, hvis vi vil legge dem sammen, kommer vi ikke til å bruke en VLOOKUP i det hele tatt. Vi kommer til å bruke en funksjon som heter SUMIF eller SUMIFS som kommer til å summere alt som samsvarer med dette elementet. Så, SUMIFS. Her er de numeriske verdiene vi vil oppsummere, og jeg trykker på F4 for å låse det. Når jeg kopierer dette ned, vil det fortsette å peke på samme område, og så vil vi sjekke og se om JOB-nummeret i kolonne A, igjen F4 der, er = til verdien til venstre for oss - i dette tilfellet E2 - og når vi kopierer det ned, vil vi se TOTALT for hvert element. (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
La oss bare gjøre en liten sjekk her. J1199. Totalt er 25365. Greit. Så det fungerer. Hvis det er tall og du vil hente alle tallene og legge dem sammen, bytt til SUMIF eller SUMIFS, men hvis det er tekst, OK, nå, er denne funksjonen ny i Office 365 i februar 2017. Så hvis du har Excel 2016 eller Excel 2013 eller Excel 2010 eller noen av de eldre, vil du ikke ha denne funksjonen. Det er en funksjon som heter TEXTJOIN. TEXTJOIN. Dette er en annen funksjon fra (Joe McDade - 01:50) som nettopp brakte oss alle de flotte dynamiske matriseformlene på Ignite i 2018, og Joe sørget for at TEXTJOIN ville fungere med arrays, noe som er veldig bra.
Så skillet her vil være, RUM, ignorere TOMT definitivt. Vi vil ignorere TOM her fordi vi kommer til å generere mange tømmer i denne neste delen, IF-setningen. HVIS det elementet over en A2, F4, er = til dette JOB-nummeret her, så vil jeg ha det tilsvarende elementet fra kolonne C, F4, ellers vil jeg ha "" slik. Lukk IF-setningen. Lukk TEXTJOIN. Må jeg trykke CONTROL + SHIFT + ENTER? Nei jeg gjør ikke. Det gir meg alle produktene som passer slik, ok? Så hvis du returnerer alle VLOOKUP-er, hvis vi vil oppsummere dem, ja, hvis vi ønsker å sammenkoble dem, ja. (= TEXTJOIN (“,”, True, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, “”)))
Ok, nå er det en annen mulighet her når folk spør meg om de kan returnere alle VLOOKUP-er. Det kan være et problem hvor vi vil slå opp hver av disse kostnadene her og finne ut HÅNDTERINGSKOSTNADEN og deretter oppsummere dem alle. Som, jeg vil ikke sette en VLOOKUP her og en VLOOKUP her og en VLOOKUP her og en VLOOKUP her. Jeg vil bare gjøre dem helt, og i så fall skal vi bruke SUM-funksjonen og deretter den gamle, gamle LOOKUP-funksjonen. LOOKUP sier at vi skal slå opp alle disse verdiene i kolonne B. Jeg trenger ikke F4 her fordi jeg ikke kopierer den noe sted. ,. Her er oppslagstabellen vår. ), lukk SUMMET, og det slukkes og gjør hver enkelt VISNING og summerer dem alle slik. (= SUM (LOOKUP (B2: B53, K3: L5)))
Vel hei. Alle disse emnene er boken min LIV: The 54 Greatest Tips of All Time. Klikk på i øverst til høyre for å lære mer.
Så spørsmålet er om du kan returnere alle VLOOKUP-er? Vel, liksom, men bruker faktisk ikke VLOOKUP. Vi skal enten bruke SUMIF, TEXTJOIN eller SUM eller LOOKUP for å løse det.
Vel hei. Jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Du vet, ok, jeg har snakket om disse dynamiske gruppene i en uke. Jeg ønsket å lage en video der jeg ikke berørte dynamiske matriser fordi jeg vet at mange ikke har dem ennå, men her er vi. Det er uttaket. Du vet, disse er ikke alfabetiske. Dette ville være så mye bedre hvis vi kunne sortere dem, og hvis du tilfeldigvis har de nye dynamiske gruppene, kan du sende dette til SORT-funksjonen, SORTERE slik, og trykke ENTER, og nå blir resultatene sortert slik.
Du vet, selv denne formelen kan bli bedre med de dynamiske gruppene. Oppslaget krever at du bruker, SANT. Hva om du ville bruke en, FALSE? Vi kan endre det til en VLOOKUP, slå opp all denne teksten i den tabellen, 2,. I dette tilfellet skal jeg bruke SANT, men i et annet tilfelle kan du bruke FALSE. KONTROLL + SKIFT + ENTER. Nei. Det kommer bare til å fungere, ok? (= SUM (VLOOKUP (B2: B53, K3: L5,2, True)))
Dynamic Arrays som kommer ut tidlig i 2019 vil løse så mange problemer.
Takk for at du hang ut gjennom uttaket her. Vi sees neste gang for en ny netcast fra.
Last ned Excel-fil
For å laste ned Excel-filen: can-you-return-all-vlookup-values.xlsx
Når noen spør "Kan VLOOKUP returnere alle kampene, er svaret nei. Men det er mange andre funksjoner som egentlig kan gjøre det samme.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke å tenke på:
"Normaliser dataene dine slik du vil at andre normaliserer dataene for deg"
Kevin Lehrbass
