Gå til hovedindhold

Vlookup og returner flere værdier baseret på et eller flere kriterier

Normalt kan du bruge Vlookup-funktionen til at få den første tilsvarende værdi, men nogle gange vil du returnere alle matchende poster baseret på et specifikt kriterium. Denne artikel vil jeg tale om, hvordan man vlookup og returnerer alle matchende værdier lodret, vandret eller i en enkelt celle.

Vlookup og returner alle tilsvarende værdier lodret

Vlookup og returner alle tilsvarende værdier vandret

Vlookup og returner alle tilsvarende værdier i en celle

Vlookup og returner alle tilsvarende værdier lodret

Hvis du vil returnere alle matchende værdier lodret baseret på et specifikt kriterium, skal du anvende følgende matrixformel:

1. Indtast eller kopier denne formel i en tom celle, hvor du vil sende resultatet:

=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))),"" )

Bemærk: I ovenstående formel C2: C20 er kolonnen indeholder den matchende post, du vil returnere; A2: A20 er kolonnen indeholder kriteriet; og E2 er det specifikke kriterium, som du vil returnere værdier baseret på. Skift dem til dit behov.

2. Tryk derefter på Ctrl + Skift + Enter nøgler sammen for at få den første værdi, og træk derefter fyldhåndtaget ned for at få alle de tilsvarende poster, som du har brug for, se skærmbillede:

tips:

For at Vlookup og returnere alle matchende værdier baseret på mere specifikke værdier lodret, skal du anvende nedenstående formel og trykke på Ctrl + Skift + Enter nøgler.

=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 og returner alle tilsvarende værdier vandret

Hvis du vil få de matchende værdier vist i vandret rækkefølge, kan nedenstående arrayformel hjælpe dig.

1. Indtast eller kopier denne formel i en tom celle, hvor du vil sende resultatet:

=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))),"")

Bemærk: I ovenstående formel C2: C20 er kolonnen indeholder den matchende post, du vil returnere; A2: A20 er kolonnen indeholder kriteriet; og F1 er det specifikke kriterium, som du vil returnere værdier baseret på. Skift dem til dit behov.

2. Tryk derefter på Ctrl + Skift + Enter nøgler sammen for at få den første værdi, og træk derefter fyldhåndtaget til højre for at få alle de tilsvarende poster, som du har brug for, se skærmbillede:

tips:

For at Vlookup og returnere alle matchende værdier baseret på mere specifikke værdier vandret, skal du anvende nedenstående formel og trykke på Ctrl + Skift + Enter nøgler.

=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 og returner alle tilsvarende værdier i en celle

For at vlookup og returnere alle tilsvarende værdier i en enkelt celle, skal du anvende følgende matrixformel.

1. Indtast eller kopier nedenstående formel til en tom celle:

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

Bemærk: I ovenstående formel C2: C20 er kolonnen indeholder den matchende post, du vil returnere; A2: A20 er kolonnen indeholder kriteriet; og F1 er det specifikke kriterium, som du vil returnere værdier baseret på. Skift dem til dit behov.

2. Tryk derefter på Ctrl + Skift + Enter nøgler sammen for at få alle matchende værdier til en enkelt celle, se skærmbillede:

tips:

For at Vlookup og returnere alle matchende værdier baseret på mere specifikke værdier i en enkelt celle, skal du anvende nedenstående formel og trykke på Ctrl + Skift + Enter nøgler.

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

Bemærk: Denne formel blev kun anvendt med succes i Excel 2016 og senere versioner. Hvis du ikke har Excel 2016, skal du se link. for at få det ned.

Flere relative Vlookup-artikler:

  • Vlookup og returner matchende data mellem to værdier i Excel
  • I Excel kan vi anvende den normale Vlookup-funktion for at få den tilsvarende værdi baseret på en given data. Men nogle gange vil vi vlookup og returnere den matchende værdi mellem to værdier, hvordan kunne du håndtere denne opgave i Excel?
  • Vlookup og returner flere værdier fra rullelisten
  • I Excel, hvordan kunne du oprette og returnere flere tilsvarende værdier fra en rulleliste, hvilket betyder, at når du vælger et element fra rullelisten, vises alle dets relative værdier på én gang som vist nedenstående skærmbillede. Denne artikel vil jeg introducere løsningen trin for trin.
  • Vlookup returnerer tomt i stedet for 0 eller ikke i Excel
  • Normalt, når du anvender vlookup-funktionen til at returnere den tilsvarende værdi, hvis din matchende celle er tom, vil den returnere 0, og hvis din matchende værdi ikke findes, får du en fejl # N / A-værdi. I stedet for at vise 0 eller # N / A-værdien, hvordan kan du få det til at vise tom celle?
  • Vlookup returnerer flere kolonner fra Excel-tabellen
  • I Excel-regneark kan du anvende Vlookup-funktionen til at returnere den matchende værdi fra en kolonne. Men nogle gange er du muligvis nødt til at udtrække matchede værdier fra flere kolonner som vist nedenstående skærmbillede. Hvordan kunne du få de tilsvarende værdier på samme tid fra flere kolonner ved hjælp af Vlookup-funktionen?
  • Vlookup-værdier på tværs af flere regneark
  • I Excel kan vi nemt anvende vlookup-funktionen til at returnere de matchende værdier i en enkelt tabel i et regneark. Men har du nogensinde overvejet, hvordan man vlookup værdi på tværs af flere regneark? Antag, at jeg har følgende tre regneark med dataudvalg, og nu vil jeg få en del af de tilsvarende værdier baseret på kriterierne fra disse tre regneark.
  • Super formel bar (let redigere flere linjer med tekst og formel); Læsning Layout (let at læse og redigere et stort antal celler); Indsæt til filtreret rækkevidde...
  • Flet celler / rækker / kolonner og opbevaring af data; Split celler indhold; Kombiner duplikatrækker og sum / gennemsnit... Forhindre duplikerede celler; Sammenlign områder...
  • Vælg Duplicate eller Unique Rækker; Vælg tomme rækker (alle celler er tomme); Super Find og Fuzzy Find i mange arbejdsbøger; Tilfældig valg ...
  • Præcis kopi Flere celler uden at ændre formelreference; Auto Opret referencer til flere ark; Indsæt kugler, Afkrydsningsfelter og mere ...
  • Foretrukne og hurtigt indsætte formler, Områder, diagrammer og billeder; Krypter celler med adgangskode Opret postliste og send e-mails ...
  • Uddrag tekst, Tilføj tekst, Fjern efter position, Fjern mellemrum; Opret og udskriv personsøgningssubtotaler; Konverter mellem celler indhold og kommentarer...
  • Superfilter (gem og anvend filterskemaer på andre ark); Avanceret sortering efter måned / uge / dag, hyppighed og mere; Specielt filter af fed, kursiv ...
  • Kombiner arbejdsbøger og arbejdsark; Fletabeller baseret på nøglekolonner; Opdel data i flere ark; Batch Konverter xls, xlsx og PDF...
  • Pivottabelgruppering efter ugenummer, ugedag og mere ... Vis ulåste, låste celler ved forskellige farver; Fremhæv celler, der har formel / navn...
kte-fane 201905
  • Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
  • Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
  • Øger din produktivitet med 50 % og reducerer hundredvis af museklik for dig hver dag!
officetab bund
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