Ga naar hoofdinhoud

Vlookup en retourneer meerdere waarden op basis van een of meerdere criteria

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.

Vlookup en retourneer alle overeenkomstige waarden verticaal

Vlookup en retourneer alle bijbehorende waarden horizontaal

Vlookup en retourneer alle bijbehorende waarden in één cel

Vlookup en retourneer alle overeenkomstige waarden verticaal

Om alle overeenkomende waarden verticaal te retourneren op basis van een specifiek criterium, past u de volgende matrixformule toe:

1. Typ of kopieer deze formule naar een lege cel waar u het resultaat wilt uitvoeren:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($E$2=$A$2:$A$20, ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Note: In de bovenstaande formule, C2:C20 is de kolom het overeenkomende record bevat dat u wilt retourneren; A2: A20 is de kolom het criterium bevat; en E2 is het specifieke criterium waarop u waarden wilt retourneren op basis van. Wijzig ze naar uw behoefte.

2. Druk vervolgens op Ctrl + Shift + Enter toetsen samen om de eerste waarde te krijgen en sleep vervolgens de vulgreep naar beneden om alle bijbehorende records te krijgen zoals je nodig hebt, zie screenshot:

Tips:

Om alle overeenkomende waarden verticaal op te zoeken en te retourneren op basis van meer specifieke waarden, past u de onderstaande formule toe en drukt u op Ctrl + Shift + Enter sleutels.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20))*(--($F$2=$B$2:$B$20))), ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Vlookup en retourneer alle bijbehorende waarden horizontaal

Als u de overeenkomende waarden in horizontale volgorde wilt weergeven, kan de onderstaande matrixformule u helpen.

1. Typ of kopieer deze formule naar een lege cel waar u het resultaat wilt uitvoeren:

=IFERROR(INDEX($C$2:$C$20,SMALL(IF($F$1=$A$2:$A$20,ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Note: In de bovenstaande formule, C2: C20 is de kolom het overeenkomende record bevat dat u wilt retourneren; A2: A20 is de kolom het criterium bevat; en F1 is het specifieke criterium waarop u waarden wilt retourneren op basis van. Wijzig ze naar uw behoefte.

2. Druk vervolgens op Ctrl + Shift + Enter toetsen samen om de eerste waarde te krijgen en sleep vervolgens de vulgreep naar rechts om alle bijbehorende records te krijgen zoals je nodig hebt, zie screenshot:

Tips:

Om alle overeenkomende waarden horizontaal op te zoeken en terug te geven op basis van meer specifieke waarden, past u de onderstaande formule toe en drukt u op Ctrl + Shift + Enter sleutels.

=IFERROR(INDEX($C$2:$C$20,SMALL(IF(1=((--($F$1=$A$2:$A$20))*(--($F$2=$B$2:$B$20))),ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Vlookup en retourneer alle bijbehorende waarden in één cel

Om alle corresponderende waarden op te vouwen en terug te sturen naar een enkele cel, moet u de volgende matrixformule toepassen.

1. Typ of kopieer de onderstaande formule naar een lege cel:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Note: In de bovenstaande formule, C2: C20 is de kolom het overeenkomende record bevat dat u wilt retourneren; A2: A20 is de kolom het criterium bevat; en F1 is het specifieke criterium waarop u waarden wilt retourneren op basis van. Wijzig ze naar uw behoefte.

2. Druk vervolgens op Ctrl + Shift + Enter toetsen samen om alle overeenkomende waarden in een enkele cel te krijgen, zie screenshot:

Tips:

Om Vlookup en alle overeenkomende waarden te retourneren op basis van meer specifieke waarden in een enkele cel, past u de onderstaande formule toe en drukt u op Ctrl + Shift + Enter sleutels.

=TEXTJOIN(", ",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Opmerking: Deze formule is alleen met succes toegepast in Excel 2016 en latere versies. Als u geen Excel 2016 heeft, kijk dan hier. om het naar beneden te krijgen.

Meer relatieve Vlookup-artikelen:

  • 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.
  • Vlookup om leeg te retourneren in plaats van 0 of n.v.t. in Excel
  • Normaal gesproken, wanneer u de functie vlookup toepast om de overeenkomstige waarde te retourneren, als uw overeenkomende cel leeg is, retourneert deze 0, en als uw overeenkomende waarde niet wordt gevonden, krijgt u een fout # N / B-waarde. In plaats van de waarde 0 of # N / A weer te geven, hoe kunt u ervoor zorgen dat er een lege cel wordt weergegeven?
  • Vlookup om meerdere kolommen uit Excel-tabel te retourneren
  • In het Excel-werkblad kunt u de functie Vlookup toepassen om de overeenkomende waarde uit één kolom te retourneren. Maar soms moet u mogelijk overeenkomende waarden uit meerdere kolommen extraheren, zoals in de volgende schermafbeelding. Hoe kun je de corresponderende waarden tegelijkertijd uit meerdere kolommen halen door de functie Vlookup te gebruiken?
  • Vlookup-waarden over meerdere werkbladen
  • In Excel kunnen we eenvoudig de vlookup-functie toepassen om de overeenkomende waarden in een enkele tabel van een werkblad te retourneren. Maar heb je er ooit over nagedacht hoe je de waarde over meerdere werkbladen kunt verdelen? Stel dat ik de volgende drie werkbladen met gegevensbereik heb, en nu wil ik een deel van de bijbehorende waarden krijgen op basis van de criteria van deze drie werkbladen.
  • 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
Comments (8)
No ratings yet. Be the first to rate!
Ahmed Abosamaha
Ahmed Abosamaha
This comment was minimized by the moderator on the site
gents

would you please share with me the formula of the three ways but for data in horizontal table.
Reply
0
0
skyyang
skyyang     Ahmed Abosamaha
This comment was minimized by the moderator on the site
Hello, Ahmed,
To solve your problem, please apply the below formulas:
Get the results vertically: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($A$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), ROW(1:1))),"" )
Get the results horizontally: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($D$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), COLUMN(A1))),"" )
Ge the results in one cell: =TEXTJOIN(", ",TRUE,IF($B$1:$K$1=J5,$B$2:$K$2,""))
Note: These formulas are array formulas, you should press Ctrl + Shift + Enter keys together to get the correct result.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-vlookup-data.png

Pease try, hope this can help you!
Reply
Report
0
0
Ahmed Abosamaha
Ahmed Abosamaha     skyyang
This comment was minimized by the moderator on the site
thanks for your greet support and quick response
Reply
0
0
itika
itika
This comment was minimized by the moderator on the site
how do u get all rows to fill up like in yr video without pressing ctrl+shift+enter
Reply
0
0
Taia
Taia
This comment was minimized by the moderator on the site
After I extend the data, some of the cells is showing a "0" instead of blank
Reply
0
0
Adam
Adam     Taia
This comment was minimized by the moderator on the site
It means that there is missing data or the wrong type of data in the cell(s) from the column that is being referrenced. For instance, Im working with a bunch of data and I want all ID numbers for workers under a certain supervisor. However, in the table column being referenced with all workers' ID numbers, some cells had the workers last name in it and not their ID numbers. Those exact cells produced 0s when the entire formula was put in, and this is because it was text within a column that is mostly numbers.
Reply
0
0
Win E
Win E
This comment was minimized by the moderator on the site
How can you tweak the formula so that you have multiple outputs in column D? E.g. I want to populate column D with each of the countries, such that all of their respective cities will be returned in E? The only work around I have can see is manually changing $D$2
Reply
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
Thank you very much, you save lots of work here!





Regarding your formula {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

I changed a little : (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





Regards,



Jeff
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations