Preskoči na glavno vsebino

Vpogled in vrni več vrednosti na podlagi enega ali več kriterijev

Avtor: Xiaoyang Zadnja sprememba: 2023-04-02

Običajno lahko s funkcijo Vlookup dobite prvo ustrezno vrednost, včasih pa želite vrniti vse ujemajoče se zapise na podlagi določenega merila. V tem članku bom govoril o tem, kako vlookup in vrnem vse ujemajoče se vrednosti navpično, vodoravno ali v eno samo celico.

Poiščite in vrnite vse ustrezne vrednosti navpično

Poiščite in vodoravno vrnite vse ustrezne vrednosti

Poiščite in vrnite vse ustrezne vrednosti v eno celico

Poiščite in vrnite vse ustrezne vrednosti navpično

Če želite vrniti vse ujemajoče se vrednosti navpično na podlagi določenega merila, uporabite naslednjo formulo matrike:

1. Vnesite ali kopirajte to formulo v prazno celico, kjer želite prikazati rezultat:

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

Opombe: V zgornji formuli, C2: C20 je stolpec vsebuje ustrezen zapis, ki ga želite vrniti; A2: A20 je stolpec vsebuje merilo; in E2 je posebno merilo, na podlagi katerega želite vrniti vrednosti. Prosimo, spremenite jih glede na vaše potrebe.

2. Nato pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvo vrednost, nato pa povlecite ročico za polnjenje navzdol, da dobite vse ustrezne zapise, kot jih potrebujete;

Nasvet:

Če želite Vlookup in vrniti vse ujemajoče se vrednosti na podlagi bolj specifičnih vrednosti navpično, uporabite spodnjo formulo in pritisnite Ctrl + Shift + Enter ključi.

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

Poiščite in vodoravno vrnite vse ustrezne vrednosti

Če želite prikazati ujemajoče se vrednosti v vodoravnem vrstnem redu, vam lahko pomaga spodnja formula matrike.

1. Vnesite ali kopirajte to formulo v prazno celico, kjer želite prikazati rezultat:

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

Opombe: V zgornji formuli, C2: C20 je stolpec vsebuje ustrezen zapis, ki ga želite vrniti; A2: A20 je stolpec vsebuje merilo; in F1 je posebno merilo, na podlagi katerega želite vrniti vrednosti. Prosimo, spremenite jih glede na vaše potrebe.

2. Nato pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvo vrednost, nato pa povlecite ročico za polnjenje desno, da dobite vse ustrezne zapise, kot jih potrebujete, glejte posnetek zaslona:

Nasvet:

Če želite Vlookup in vodoravno vrniti vse ujemajoče se vrednosti na podlagi bolj specifičnih vrednosti, uporabite spodnjo formulo in pritisnite Ctrl + Shift + Enter ključi.

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

Poiščite in vrnite vse ustrezne vrednosti v eno celico

Če želite vlookup in vrniti vse ustrezne vrednosti v eno celico, uporabite naslednjo formulo matrike.

1. Vnesite ali kopirajte spodnjo formulo v prazno celico:

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

Opombe: V zgornji formuli, C2: C20 je stolpec vsebuje ustrezen zapis, ki ga želite vrniti; A2: A20 je stolpec vsebuje merilo; in F1 je posebno merilo, na podlagi katerega želite vrniti vrednosti. Prosimo, spremenite jih glede na vaše potrebe.

2. Nato pritisnite Ctrl + Shift + Enter , da dobite vse ujemajoče se vrednosti v eno celico, glejte posnetek zaslona

Nasvet:

Če želite Vlookup in vrniti vse ujemajoče se vrednosti na podlagi bolj specifičnih vrednosti v eni celici, uporabite spodnjo formulo in pritisnite Ctrl + Shift + Enter ključi.

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

Opomba: Ta formula se je uspešno uporabila samo v Excelu 2016 in novejših različicah. Če nimate Excela 2016, si ga oglejte tukaj da se spusti.

Več relativnih člankov o Vlookupu:

  • Iskanje in vrnitev več vrednosti s spustnega seznama
  • Kako lahko v Excelu iščete in vrnete več ustreznih vrednosti s spustnega seznama, kar pomeni, da ko izberete en element s spustnega seznama, se naenkrat prikažejo vse njegove relativne vrednosti, kot je prikazano na sliki spodaj. V tem članku bom predstavil rešitev korak za korakom.
  • Vlookup vrne prazen namesto 0 ali N / A v Excelu
  • Ko uporabite funkcijo vlookup za vrnitev ustrezne vrednosti, če je vaša ujemajoča se celica prazna, bo vrnila 0, in če vaše ujemajoče se vrednosti ne najdete, boste dobili vrednost napake # N / A. Namesto prikaza vrednosti 0 ali # N / A, kako lahko naredite, da se prikaže prazna celica?
  • Vlookup za vrnitev več stolpcev iz Excelove tabele
  • V Excelovem delovnem listu lahko uporabite funkcijo Vlookup, da vrnete ujemajočo se vrednost iz enega stolpca. Toda včasih boste morda morali iz več stolpcev izvleči ujemajoče se vrednosti, kot je prikazano na spodnji sliki zaslona. Kako lahko s pomočjo funkcije Vlookup istočasno dobite ustrezne vrednosti iz več stolpcev?
  • Vrednosti iskanja v več delovnih listih
  • V Excelu lahko enostavno uporabimo funkcijo vlookup za vrnitev ustreznih vrednosti v eni tabeli delovnega lista. Ampak, ali ste že kdaj pomislili, kako vlookup vrednost v več delovnih listov? Recimo, da imam naslednje tri delovne liste z obsegom podatkov, zdaj pa želim dobiti del ustreznih vrednosti na podlagi meril iz teh treh delovnih listov.
  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce in vodenje podatkov; Vsebina razdeljenih celic; Združite podvojene vrstice in vsoto / povprečje... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Priljubljene in hitro vstavite formule, Obsegi, grafikoni in slike; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Razvrščanje vrtilne tabele po številka tedna, dan v tednu in še več ... Prikaži odklenjene, zaklenjene celice po različnih barvah; Označite celice s formulo / imenom...
zavihek kte 201905
  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
dno pisarniške mize
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