VERT.ZOEKEN is een krachtige functie. Maar ik krijg vaak een vraag in een van mijn Power Excel-seminars van iemand die wil weten of VERT.ZOEKEN alle overeenkomende waarden kan retourneren. Zoals u weet, retourneert VERT.ZOEKEN met False als vierde argument altijd de eerste gevonden overeenkomst. In de volgende schermafbeelding retourneert cel F2 3623 omdat dit de eerste overeenkomst is die is gevonden voor taak J1199.
De vraag kan dan: kan VERT.ZOEKEN alle overeenkomsten retourneren?
VERT.ZOEKEN zal niet. Maar andere functies kunnen dat wel.
Als u alle kosten van baan J1199 wilt optellen, gebruikt u =SUMIFS($B$2:$B$53,$A$2:$A$53,G2)
:
Als u tekstwaarden heeft en alle resultaten in één waarde wilt samenvoegen, kunt u =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,""))
. Deze formule werkt alleen in Office 365 en Excel 2019.
Of misschien moet u alle resultaten voor een enkele taak in een nieuw bereik van het werkblad plaatsen. Een gloednieuwe =FILTER(B2:C53,A2:A53=K1,"None Found")
functie die in 2019 naar Office 365 komt, lost het probleem op:
Soms willen mensen alle VERT.ZOEKEN uitvoeren en ze optellen. Als uw opzoektabel is gesorteerd, kunt u =SUM(LOOKUP(B2:B53,M3:N5))
.
Als u alle VERT.ZOEKEN moet optellen met de Exact Match-versie van VERT.ZOEKEN, moet u toegang hebben tot dynamische arrays om =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE))
.
Raadpleeg Excel Dynamic Arrays Straight To The Point voor meer informatie over dynamische arrays.
Bekijk video
Videotranscriptie
Leer Excel van, Podcast-aflevering 2247: kunt u alle VLookUp-waarden retourneren?
Hallo. Welkom terug bij de netcast. Ik ben Bill Jelen. Twee vragen kwamen vorige week naar voren tijdens mijn seminar in Appleton, Wisconsin - beide gerelateerd. Ze zeiden, hé, hoe kunnen we alle VERT.ZOEKEN retourneren, oké? In dit geval, zoals J1199 heeft een heleboel lucifers en ze, weet je, willen ze allemaal terugsturen, en mijn eerste vraag wanneer iemand me dit stelt, is: wat wil je doen met de lucifers? Zijn het cijfers die u wilt optellen of is het tekst die u wilt samenvoegen? En het is grappig. De twee vragen in hetzelfde seminar, de ene persoon wilde ze optellen en de andere persoon wilde de resultaten samenvoegen.
Dus laten we deze beide eens bekijken. Kijk in de YouTube-beschrijving voor een inhoudsopgave waar je naar de andere kunt springen als je het resultaat van de tekst wilt zien.
Oké, dus ten eerste, als we ze allemaal willen optellen, gaan we helemaal geen VERT.ZOEKEN gebruiken. We gaan een functie gebruiken genaamd SUMIF of SUMIFS die alles wat overeenkomt met dit item optelt. Dus, SUMIFS. Hier zijn de numerieke waarden die we willen optellen en ik druk op F4 om dat vast te zetten. Op die manier, terwijl ik dit kopieer, blijft het naar hetzelfde bereik wijzen, en dan willen we gaan kijken of het JOB-nummer in kolom A, opnieuw F4 daar, = is naar de waarde links van ons - in dit geval E2 - en terwijl we dat kopiëren, zien we het TOTAAL voor elk item. (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Laten we hier even een kleine check doen. J1199. Het totaal is 25365. Oké. Dus dat werkt. Als het cijfers zijn en je wilt alle cijfers ophalen en ze optellen, schakel dan over naar SUMIF of SUMIFS, maar als het om tekst gaat, oké, nu is deze functie nieuw in Office 365 in februari 2017. Dus als je Excel 2016 hebt of Excel 2013 of Excel 2010 of een van die oudere, u zult deze functie niet hebben. Het is een functie genaamd TEXTJOIN. TEXTJOIN. Dit is een andere functie van (Joe McDade - 01:50) die ons zojuist al die geweldige dynamische array-formules bij Ignite in 2018 bracht, en Joe zorgde ervoor dat TEXTJOIN zou werken met arrays, wat echt geweldig is.
Dus het scheidingsteken hier is, RUIMTE, negeer LEEG beslist. We willen EMPTY hier negeren, omdat we in dit volgende deel, de IF-instructie, veel leeggoed zullen genereren. ALS dat item boven een A2, F4, = is voor dit JOB-nummer hier, dan wil ik het corresponderende item uit kolom C, F4, anders wil ik "" zo. Sluit die IF-verklaring. Sluit het TEXTJOIN. Moet ik op CONTROL + SHIFT + ENTER drukken? Nee, dat doe ik niet. Het brengt me alle producten die zo bij elkaar passen, oké? Dus alle VERT.ZOEKEN retourneren, als we ze willen optellen, ja, als we ze willen samenvoegen, ja. (= TEXTJOIN (",", True, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, "")))
Oké, nu is er nog een andere mogelijkheid als mensen mij vragen of ze alle VERT.ZOEKEN kunnen retourneren. Het kan een probleem zijn waarbij we elk van deze kosten hier willen opzoeken en de VERWERKINGSKOSTEN willen berekenen en ze vervolgens allemaal willen samenvatten. Zoals, ik wil hier geen VERT.ZOEKEN plaatsen en hier een VERT.ZOEKEN en hier een VERT.ZOEKEN en hier een VERT.ZOEKEN. Ik wil ze gewoon helemaal doen en in dat geval gaan we de SOM-functie gebruiken en vervolgens de oude, oude LOOKUP-functie. LOOKUP zegt dat we al deze waarden in kolom B gaan opzoeken. Ik heb F4 hier niet nodig omdat ik het nergens kopieer. ,. Hier is onze opzoektabel. ), sluit de SOM, en deze gaat uit en voert elke individuele VERT.ZOEKEN uit en somt ze allemaal op die manier op. (= SOM (ZOEKEN (B2: B53, K3: L5)))
Hallo daar. Al deze onderwerpen zijn mijn boek LIV: The 54 Greatest Tips Of All Time. Klik op die i in de rechterbovenhoek voor meer informatie.
Dus de vraag is: kun je alle VERT.ZOEKEN retourneren? Nou ja, een beetje, maar niet echt VERT.ZOEKEN gebruiken. We gaan SUMIF, TEXTJOIN of SUM of LOOKUP gebruiken om het op te lossen.
Hallo daar. Ik wil je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Weet je, oké, ik heb het al een week over deze dynamische arrays. Ik wilde een video maken waarin ik dynamische arrays niet heb aangeroerd, omdat ik weet dat veel mensen ze nog niet hebben, maar hier zijn we dan. Het is de outtake. Weet je, deze zijn niet alfabetisch. Dit zou zoveel beter zijn als we ze zouden kunnen sorteren, en als je toevallig de nieuwe dynamische arrays hebt, zou je deze naar de SORT-functie kunnen sturen, SORT op deze manier, en op ENTER drukken, en nu worden de resultaten zo gesorteerd.
Weet je, zelfs deze formule zou beter kunnen worden met de dynamische arrays. De zoekactie vereist dat u de, TRUE gebruikt. Wat als u een, FALSE wilde gebruiken? We zouden dat kunnen veranderen in een VERT.ZOEKEN, al deze tekst opzoeken in die tabel, 2,. In dit geval ga ik TRUE gebruiken, maar in een ander geval zou je FALSE kunnen gebruiken. CONTROL + SHIFT + ENTER. Nee. Het gaat gewoon werken, oké? (= SOM (VERT.ZOEKEN (B2: B53, K3: L5,2, Waar)))
Dynamische arrays die begin 2019 uitkomen, zullen zoveel problemen oplossen.
Bedankt dat je hier rondhangt. We zien je de volgende keer voor een nieuwe netcast van.
Download Excel-bestand
Om het Excel-bestand te downloaden: can-you-return-all-vlookup-values.xlsx
Als iemand vraagt "Kan VERT.ZOEKEN alle overeenkomsten retourneren, is het antwoord Nee. Maar er zijn veel andere functies die in wezen hetzelfde kunnen doen.
Excel-gedachte van de dag
Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken:
"Normaliseer uw gegevens zoals u anderen hun gegevens voor u zou laten normaliseren"
Kevin Lehrbass