Hoppa till huvudinnehåll

Vlookup och returnera flera värden baserat på ett eller flera kriterier

Normalt kan du använda Vlookup-funktionen för att få det första motsvarande värdet, men ibland vill du returnera alla matchande poster baserat på ett specifikt kriterium. Den här artikeln kommer jag att prata om hur man slår på och returnerar alla matchande värden vertikalt, horisontellt eller i en enda cell.

Vlookup och returnera alla motsvarande värden vertikalt

Vlookup och returnera alla motsvarande värden horisontellt

Vlookup och returnera alla motsvarande värden i en cell

Vlookup och returnera alla motsvarande värden vertikalt

För att returnera alla matchande värden vertikalt baserat på ett specifikt kriterium, använd följande matrisformel:

1. Ange eller kopiera den här formeln till en tom cell där du vill skicka 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))),"" )

Anmärkningar: I ovanstående formel, C2: C20 är att kolumnen innehåller den matchande posten du vill returnera; A2: A20 är kolumnen innehåller kriteriet; och E2 är det specifika kriteriet som du vill returnera värden baserat på. Ändra dem efter dina behov.

2. Tryck sedan på Ctrl + Skift + Enter tangenterna tillsammans för att få det första värdet och dra sedan ned fyllningshandtaget för att få alla motsvarande poster som du behöver, se skärmdump:

Tips:

För att Vlookup och returnera alla matchande värden baserat på mer specifika värden vertikalt, använd formeln nedan och tryck på Ctrl + Skift + Enter nycklar.

=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 och returnera alla motsvarande värden horisontellt

Om du vill få matchande värden i horisontell ordning kan nedanstående arrayformel hjälpa dig.

1. Ange eller kopiera den här formeln till en tom cell där du vill skicka 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))),"")

Anmärkningar: I ovanstående formel, C2: C20 är att kolumnen innehåller den matchande posten du vill returnera; A2: A20 är kolumnen innehåller kriteriet; och F1 är det specifika kriteriet som du vill returnera värden baserat på. Ändra dem efter dina behov.

2. Tryck sedan på Ctrl + Skift + Enter för att få det första värdet och dra sedan fyllningshandtaget åt höger för att få alla motsvarande poster som du behöver, se skärmdump:

Tips:

För att Vlookup och returnera alla matchande värden baserat på mer specifika värden horisontellt, använd formeln nedan och tryck på Ctrl + Skift + Enter nycklar.

=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 och returnera alla motsvarande värden i en cell

För att slå upp och returnera alla motsvarande värden i en enda cell, bör du använda följande matrisformel.

1. Ange eller kopiera formeln nedan till en tom cell:

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

Anmärkningar: I ovanstående formel, C2: C20 är att kolumnen innehåller den matchande posten du vill returnera; A2: A20 är kolumnen innehåller kriteriet; och F1 är det specifika kriteriet som du vill returnera värden baserat på. Ändra dem efter dina behov.

2. Tryck sedan på Ctrl + Skift + Enter tangenter tillsammans för att få alla matchande värden i en enda cell, se skärmdump:

Tips:

För att söka upp och returnera alla matchande värden baserat på mer specifika värden i en enda cell, använd formeln nedan och tryck på Ctrl + Skift + Enter nycklar.

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

Notera: Denna formel tillämpades bara framgångsrikt i Excel 2016 och senare versioner. Om du inte har Excel 2016, se här. för att få ner det.

Mer relativa Vlookup-artiklar:

  • Vlookup och returnera flera värden från rullgardinslistan
  • I Excel, hur kan du söka efter och returnera flera motsvarande värden från en rullgardinslista, vilket innebär att när du väljer ett objekt från listrutan, visas alla dess relativa värden på en gång som följande skärmdump visas. Den här artikeln presenterar jag lösningen steg för steg.
  • Vlookup ska returnera tomt istället för 0 eller ej tillgängligt i Excel
  • Normalt, när du använder vlookup-funktionen för att returnera motsvarande värde, om din matchande cell är tom, kommer den att returnera 0, och om ditt matchningsvärde inte hittas får du ett felnummer N / A-värde. Istället för att visa 0 eller # N / A-värdet, hur kan du få det att visa tom cell?
  • Vlookup för att returnera flera kolumner från Excel-tabellen
  • I Excel-kalkylbladet kan du använda Vlookup-funktionen för att returnera matchande värde från en kolumn. Men ibland kan du behöva extrahera matchade värden från flera kolumner enligt följande skärmdump. Hur kunde du få motsvarande värden samtidigt från flera kolumner med hjälp av Vlookup-funktionen?
  • Vlookup-värden över flera kalkylblad
  • I Excel kan vi enkelt använda vlookup-funktionen för att returnera matchande värden i en enda tabell i ett kalkylblad. Men har du någonsin funderat på hur hur man kan värma om flera kalkylblad? Antag att jag har följande tre kalkylblad med dataomfång, och nu vill jag få en del av motsvarande värden baserat på kriterierna från dessa tre kalkylblad.
  • Super Formula Bar (enkelt redigera flera rader med text och formel); Läslayout (enkelt läsa och redigera ett stort antal celler); Klistra in i filtrerat intervall...
  • Sammanfoga celler / rader / kolumner och förvaring av data; Delat cellinnehåll; Kombinera duplicerade rader och summa / genomsnitt... Förhindra duplicerade celler; Jämför intervall...
  • Välj Duplicera eller Unikt Rader; Välj tomma rader (alla celler är tomma); Super Find och Fuzzy Find i många arbetsböcker; Slumpmässigt val ...
  • Exakt kopia Flera celler utan att ändra formelreferens; Skapa referenser automatiskt till flera ark; Sätt in kulor, Kryssrutor och mer ...
  • Favorit och sätt snabbt in formler, Intervall, diagram och bilder; Kryptera celler med lösenord; Skapa e-postlista och skicka e-post ...
  • Extrahera text, Lägg till text, ta bort efter position, Ta bort mellanslag; Skapa och skriva ut personsökningstalsatser; Konvertera mellan celler innehåll och kommentarer...
  • Superfilter (spara och tillämpa filterscheman på andra ark); Avancerad sortering efter månad / vecka / dag, frekvens och mer; Specialfilter av fet, kursiv ...
  • Kombinera arbetsböcker och arbetsblad; Sammanfoga tabeller baserat på nyckelkolumner; Dela data i flera ark; Batchkonvertera xls, xlsx och PDF...
  • Gruppering av pivottabell efter veckonummer, veckodagen och mer ... Visa olåsta, låsta celler av olika färger; Markera celler som har formel / namn...
kte-flik 201905
  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje dag!
officetab botten
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