Note: The other languages of the website are Google-translated. Back to English

Hoe meerdere overeenkomende waarden retourneren op basis van een of meerdere criteria in Excel?

Normaal gesproken is het opzoeken van een specifieke waarde en het retourneren van het overeenkomende item voor de meesten van ons eenvoudig met de functie VERT.ZOEKEN. Maar heb je ooit geprobeerd om meerdere overeenkomende waarden te retourneren op basis van een of meer criteria zoals in het volgende screenshot? In dit artikel zal ik enkele formules introduceren om deze complexe taak in Excel op te lossen.

Retourneer meerdere overeenkomende waarden op basis van een of meerdere criteria met matrixformules


Retourneer meerdere overeenkomende waarden op basis van een of meerdere criteria met matrixformules

Ik wil bijvoorbeeld alle namen extraheren van wie de leeftijd 28 is en uit de Verenigde Staten komt, pas de volgende formule toe:

1. Kopieer of voer de onderstaande formule in een lege cel in waar u het resultaat wilt vinden:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Opmerking:: In de bovenstaande formule, B2: B11 is de kolom waaruit de overeenkomende waarde wordt geretourneerd; F2, C2: C11 zijn de eerste voorwaarde en de kolomgegevens die de eerste voorwaarde bevatten; G2, D2: D11 zijn de tweede voorwaarde en de kolomgegevens die deze voorwaarde bevatten, wijzig deze dan naar wens.

2. Druk vervolgens op Ctrl + Shift + Enter toetsen om het eerste overeenkomende resultaat te krijgen, en selecteer vervolgens de eerste formulecel en sleep de vulgreep naar beneden naar de cellen totdat de foutwaarde wordt weergegeven, nu worden alle overeenkomende waarden geretourneerd zoals onderstaand screenshot:

Tips: Als u alleen alle overeenkomende waarden op basis van één voorwaarde wilt retourneren, past u de onderstaande matrixformule toe:

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


Meer relatieve artikelen:

  • Retourneer meerdere opzoekwaarden in één door komma's gescheiden cel
  • In Excel kunnen we de functie VERT.ZOEKEN toepassen om de eerste overeenkomende waarde uit een tabelcel te retourneren, maar soms moeten we alle overeenkomende waarden extraheren en vervolgens gescheiden door een specifiek scheidingsteken, zoals komma, streepje, enz ... in een enkele cel zoals in het volgende screenshot getoond. Hoe kunnen we meerdere opzoekwaarden ophalen en retourneren in één door komma's gescheiden cel in Excel?
  • Bekijk en retourneer meerdere overeenkomende waarden tegelijk in Google Sheet
  • De normale Vlookup-functie in het Google-blad kan u helpen de eerste overeenkomende waarde te vinden en te retourneren op basis van bepaalde gegevens. Maar soms moet u mogelijk alle overeenkomende waarden opvullen en retourneren zoals in het volgende screenshot. Heeft u goede en gemakkelijke manieren om deze taak in het Google-blad op te lossen?
  • Bekijk en retourneer meerdere waarden uit de vervolgkeuzelijst
  • Hoe kunt u in Excel meerdere overeenkomstige waarden uit een vervolgkeuzelijst opvouwen en retourneren, wat betekent dat wanneer u een item uit de vervolgkeuzelijst kiest, alle relatieve waarden tegelijk worden weergegeven zoals in het volgende screenshot. In dit artikel zal ik de oplossing stap voor stap introduceren.
  • Bekijk en retourneer meerdere waarden verticaal in Excel
  • Normaal gesproken kunt u de functie Vlookup gebruiken om de eerste overeenkomstige waarde te krijgen, maar soms wilt u alle overeenkomende records retourneren op basis van een specifiek criterium. In dit artikel zal ik het hebben over het opvullen en retourneren van alle overeenkomende waarden verticaal, horizontaal of in één enkele cel.
  • Zoek en retourneer overeenkomende gegevens tussen twee waarden in Excel
  • In Excel kunnen we de normale Vlookup-functie toepassen om de bijbehorende waarde te krijgen op basis van bepaalde gegevens. Maar soms willen we opvullen en de overeenkomende waarde tussen twee waarden retourneren, zoals de volgende schermafbeelding laat zien, hoe zou u deze taak in Excel kunnen aanpakken?

 


  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen en het bewaren van gegevens; Gespleten cellen inhoud; Combineer dubbele rijen en som / gemiddelde... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Favoriete formules en snel invoegen, Bereiken, grafieken en afbeeldingen; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Draaitabel groeperen op weeknummer, dag van de week en meer ... Toon ontgrendelde, vergrendelde cellen door verschillende kleuren; Markeer cellen met formule / naam...
kte tabblad 201905
  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!
officetab onderkant

 

Heb je vragen? Stel ze hier. (25)
Nog geen beoordelingen. Beoordeel als eerste!
Deze opmerking is gemaakt door de moderator op de site
Ik heb exact dezelfde formule geprobeerd; 100% gekopieerd. Het enige dat ik heb gewijzigd, was dat de gegevens werden gematcht en geretourneerd. Wanneer ik deze formule gebruik, zegt Excel "Je hebt te veel argumenten ingevoerd voor deze functie). =INDEX('2020 Volume Report'!$B$3:$B$100,SMALL(IF(COUNTIF($A$1,'2020 Volume) Rapport'!$A$3:$A$100)*COUNTIF($A$3,'Volumerapport 2020'!$D$3:$D$100),ROW('Volumerapport 2020'!$A$3:$G$100)- MIN(ROW('Volumerapport 2020'!$A$3:$G$100))+1,"0"),RIJ(A1),COLUMN(A1))
Deze opmerking is gemaakt door de moderator op de site
Hallo, kunt u uw gegevens en formulefout hier als een schermafbeelding geven?
Deze opmerking is gemaakt door de moderator op de site
Hallo, hoe kan ik het gebruiken voor horizontale toestand.
Deze opmerking is gemaakt door de moderator op de site
Wat is de "0" na de +1 in de formule? Dat staat niet in het voorbeeld.
Deze opmerking is gemaakt door de moderator op de site
Hallo, ik had dezelfde formule geprobeerd. krijg resultaat, maar wanneer CSE wordt gegeven, worden er geen meerdere antwoorden gegeven
Deze opmerking is gemaakt door de moderator op de site

Deze opmerking is gemaakt door de moderator op de site
Met betrekking tot het retourneren van meerdere overeenkomende waarden op basis van één of meerdere criteria met matrixformules: waarom werkt het niet als ik de gegevens ergens anders heb, behalve als ik in A1 begin, ook al werk ik alle celverwijzingen in de formule bij?
Deze opmerking is gemaakt door de moderator op de site
Welke wijziging in de formule zou in het eerste voorbeeld nodig zijn om iedereen die jonger was dan 28 jaar terug te geven?
Deze opmerking is gemaakt door de moderator op de site
Hoi,

Ik vroeg me af of het überhaupt mogelijk is om een ​​2e criterium in te voeren, maar uit hetzelfde bereik als het 1e criterium,

Met het gebruikte voorbeeld hierboven zou ik bijvoorbeeld willen zoeken naar de namen van mensen uit zowel Amerika als Frankrijk. Dus cel F3 zou Frankrijk hebben, Scarlett & Andrew zouden ook in de lijst in kolom G worden ingevuld

Bij voorbaat dank voor de hulp.
Deze opmerking is gemaakt door de moderator op de site
Hallo Nick,

Blij om te helpen. Als je de namen van mensen uit zowel Amerika als Frankrijk wilt krijgen, raad ik je aan om onze formule twee keer te gebruiken om het resultaat te krijgen. Zie de screenshot, In F2 en G2 zijn waarden "Verenigde Staten" en "Frankrijk". Formule toepassen =IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1 ), ROW(1:1))),"" ) om de resultaten voor Amerika te krijgen. En pas de formule toe =IFERROR(INDEX($B$2:$B$11, SMALL(IF($G$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+ 1), ROW(1:1))),"" ) om de resultaten voor Frankrijk te krijgen. Het is makkelijk. Probeer het alsjeblieft.

Hoogachtend,
Mandy
Deze opmerking is gemaakt door de moderator op de site
Wanneer ik de tweede formule gebruik en naar beneden sleep, verschijnt er niets. Het resultaat van de formule (fx) zegt dat het iets zou moeten retourneren, maar het is leeg. Hoe corrigeer ik dit?
Deze opmerking is gemaakt door de moderator op de site
Hallo Alysia,

Blij om te helpen. Ik probeerde de tweede formule in het artikel en sleepte de formule naar beneden, de rest van de resultaten werden geretourneerd. Ik denk dat er twee redenen kunnen zijn voor uw probleem. Ten eerste vergeet je misschien om op de toetsen Ctrl + Shift + Enter te drukken om de formule in te voeren. Ten tweede is het overeenkomende resultaat slechts één, dus er worden geen andere resultaten geretourneerd. Gelieve een cheque te hebben.

Hoogachtend,
Mandy
Deze opmerking is gemaakt door de moderator op de site
Hallo,
Ik heb geprobeerd de formule te gebruiken en deze genereert een waarde van 0 of de bijgevoegde afbeelding
Deze opmerking is gemaakt door de moderator op de site
Hallo, Milku
Uw screenshot toonde WPS-software van de MAC-versie, dus ik weet niet zeker of onze formule beschikbaar is.
Ik heb hier een Excel-bestand naar toe geüpload, je kunt proberen of het in jouw omgeving correct kan worden berekend.
Dank je!
Deze opmerking is gemaakt door de moderator op de site
Hallo,
wat zou er nodig zijn om de eerste formule uit te breiden in het volgende geval:
Sommige ID's zijn leeg (bijv. cel A5 is leeg) en ik zou graag een extra voorwaarde willen om alleen regels uit te voeren als de ID's niet leeg zijn. (Dus de output zou dan James en Abdul moeten zijn.
Bedankt!
Deze opmerking is gemaakt door de moderator op de site
Hallo, Jo,
Gebruik de onderstaande formule om uw probleem op te lossen:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Neem alsjeblieft een ry, hoop dat het je kan helpen!
Deze opmerking is gemaakt door de moderator op de site
Hoi,

als ik in cel H1 "Naam" schrijf en dat aan de formule wil koppelen, hoe zou dat dan werken?
Dan zou ik "ID" in cel H1 kunnen schrijven en zou automatisch als resultaat krijgen: AA1004; DD1009; PP1023 (voor de eerste formule)

Dank u bij voorbaat!
Deze opmerking is gemaakt door de moderator op de site
Hallo, Marie
Sorry, ik begrijp het punt van je eerste probleem niet, zou je je probleem duidelijker en gedetailleerder kunnen uitleggen? Of u kunt hier een screenshot invoegen om uw probleem te beschrijven.
Wat betreft de tweede vraag, u hoeft alleen de celverwijzing als volgt te wijzigen:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Vergeet niet om op te drukken Ctrl + Shift + Enter toetsen samen.
Probeer het alsjeblieft, ik hoop dat het je kan helpen!
Deze opmerking is gemaakt door de moderator op de site
Heyi, bedankt voor de formule. Het werkte voor "vaste" waarden / tekst als criteria. Een van de criteria die ik probeer te gebruiken, is echter een voorwaarde (waarden <>0 ), maar werkt niet volgens de beschreven formule. Weten jullie wat ik moet veranderen om de formule aan te passen, zodat ik een voorwaarde als een van de criteria kan hebben?

Beste,

John
Deze opmerking is gemaakt door de moderator op de site
Hallo, Marcus
Bekijk dit artikel om uw probleem op te lossen:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
Er zijn enkele gedetailleerde uitleg van deze taak. U hoeft alleen de criteira in uw eigen criteria te veranderen.
Dank je!
Deze opmerking is gemaakt door de moderator op de site
Hoi,

Allereerst bedankt voor het delen!

Kunt u alstublieft een oplossing bieden voor het onderstaande geval:

Ik heb 3 kolommen (A: Bevat referentie-informatie, B: Bevat informatie die moet worden doorzocht, C: Zoekresultaat)

Afbeeldings-URL wordt hieronder gegeven

https://ibb.co/VHCd09K

Kolom A------------------------- Kolom B------------ Kolom C
Bestandsnaam ------------------------- Naam----------------Bestandsnaam, documentnaam, Elementnaam, naam
Gewijzigd element ----------------- Element--------------Gewijzigd element, elementnaam, element-ID
Kolomlocatie
Document Naam
Elementnaam
Naam
Categorie
Garantie
helling
Element-ID

Wat ik nodig heb, is in kolom A zoeken naar een gedeeltelijke overeenkomst met cel B2 (Naam) of B3 (Element) en het resultaat in één cel krijgen,

Dank je, Behzad
Deze opmerking is gemaakt door de moderator op de site
Hallo, Behzad
Misschien kan de onderstaande door de gebruiker gedefinieerde functie u helpen.
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


Kopieer en plak deze code en gebruik vervolgens deze formule:=ConcatPartLookUp(B2,$A$2:$A$8) om het gewenste resultaat te krijgen.
Probeer het alsjeblieft, ik hoop dat het je kan helpen!
Deze opmerking is gemaakt door de moderator op de site
Hoi,

Bedankt voor het plaatsen van deze voorbeelden.
Ik probeer dit in mijn eigen blad te implementeren, maar krijg het niet werkend (misschien omdat ik een europese versie van Excel gebruik)?

Ik wil de data krijgen van de dagen dat ik mijn diensten had of dat ik 'enkele' (>0) uren voor een klant heb gewerkt.

Dus in I3 staat de naam en in J3 de maand. K3 en L3 zijn de ploegen (1 is gewerkt) en uren (weet niet hoe dit in te stellen, zou meer dan nul moeten zijn)

Mijn verwachte resultaten zijn in:
Verschuivingen: I7 en I8
uur: J7

Ik heb dus meer dan 0 uur gewerkt voor 'persoon 2' in oktober op 3-10-2022
had diensten voor persoon 2 op '10-10-2022' en 28-10-2022

Wanneer ik toevoeg '=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW ($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))' in mijn Excel-blad, het staat de komma tussen de verschillende delen van de formule.
Dus ik moet ze veranderen in ';'.
Maar als ik het probeer, staat er altijd: '#NAAM?'

Dus kan iemand mij hiermee helpen?

Vriendelijke groeten,

Bas
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
Deze opmerking is gemaakt door de moderator op de site
Hallo, als er dubbele waarden zijn (bijvoorbeeld twee adams), hoe zorg ik er dan voor dat er slechts 1 adam wordt geretourneerd en niet 2?
Deze opmerking is gemaakt door de moderator op de site
Hallo, Bobby,
Om alleen unieke overeenkomende waarden te extraheren, moet u de onderstaande formule toepassen:
Druk na het plakken van de formule op Ctrl + Shift + Enter toetsen samen om het juiste resultaat te krijgen.
=ALS.FOUT(INDEX($B$2:$B$5, VERGELIJKEN(0, AANTAL.ALS(H1:$H$1, $B$2:$B$5)+ALS($D$2:$D$5<>$G$2, 1 , 0)+IF($C$2:$C$5<>$F$2, 1, 0), 0)), "")

Probeer het alsjeblieft, ik hoop dat het je kan helpen!
Er zijn nog geen reacties geplaatst
Laat uw commentaar
Posten als gast
×
Beoordeel dit bericht:
0   Personages
Voorgestelde locaties

Volg ons

Copyright © 2009 - www.extendoffice.com. | Alle rechten voorbehouden. Aangedreven door ExtendOffice. | Sitemap
Microsoft en het Office-logo zijn handelsmerken of gedeponeerde handelsmerken van Microsoft Corporation in de Verenigde Staten en / of andere landen.
Beschermd door Sectigo SSL