Salt la conținutul principal

Căutați și returnați mai multe valori pe baza unuia sau mai multor criterii

În mod normal, puteți utiliza funcția Vlookup pentru a obține prima valoare corespunzătoare, dar, uneori, doriți să returnați toate înregistrările de potrivire pe baza unui criteriu specific. În acest articol, voi vorbi despre cum să vizualizați și să returnați toate valorile potrivite pe verticală, orizontală sau într-o singură celulă.

Căutați și returnați toate valorile corespunzătoare pe verticală

Căutați și returnați toate valorile corespunzătoare pe orizontală

Căutați și returnați toate valorile corespunzătoare într-o singură celulă

Căutați și returnați toate valorile corespunzătoare pe verticală

Pentru a returna toate valorile potrivite pe verticală pe baza unui criteriu specific, vă rugăm să aplicați următoarea formulă matrice:

1. Introduceți sau copiați această formulă într-o celulă goală unde doriți să afișați rezultatul:

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

notițe: În formula de mai sus, C2: C20 este coloana conține înregistrarea potrivită pe care doriți să o returnați; A2: A20 este coloana conține criteriul; și E2 este criteriul specific pe care doriți să returnați valori bazate pe. Vă rugăm să le schimbați după nevoile dvs.

2. Apoi, apăsați Ctrl + Shift + Enter tastele împreună pentru a obține prima valoare, apoi glisați mânerul de umplere în jos pentru a obține toate înregistrările corespunzătoare după cum aveți nevoie, vedeți captura de ecran:

Sfat:

Pentru a căuta și a returna toate valorile potrivite pe baza unor valori mai specifice pe verticală, aplicați formula de mai jos și apăsați Ctrl + Shift + Enter chei.

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

Căutați și returnați toate valorile corespunzătoare pe orizontală

Dacă doriți să obțineți valorile potrivite afișate în ordine orizontală, formula matricei de mai jos vă poate ajuta.

1. Introduceți sau copiați această formulă într-o celulă goală unde doriți să afișați rezultatul:

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

notițe: În formula de mai sus, C2: C20 este coloana conține înregistrarea potrivită pe care doriți să o returnați; A2: A20 este coloana conține criteriul; și F1 este criteriul specific pe care doriți să returnați valori bazate pe. Vă rugăm să le schimbați după nevoile dvs.

2. Apoi, apăsați Ctrl + Shift + Enter tastele împreună pentru a obține prima valoare, apoi glisați mânerul de umplere spre dreapta pentru a obține toate înregistrările corespunzătoare după cum aveți nevoie, vedeți captura de ecran:

Sfat:

Pentru a căuta și a returna toate valorile potrivite pe baza unor valori mai specifice pe orizontală, aplicați formula de mai jos și apăsați Ctrl + Shift + Enter chei.

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

Căutați și returnați toate valorile corespunzătoare într-o singură celulă

Pentru a vizualiza și a returna toate valorile corespunzătoare într-o singură celulă, ar trebui să aplicați următoarea formulă matrice.

1. Introduceți sau copiați formula de mai jos într-o celulă goală:

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

notițe: În formula de mai sus, C2: C20 este coloana conține înregistrarea potrivită pe care doriți să o returnați; A2: A20 este coloana conține criteriul; și F1 este criteriul specific pe care doriți să returnați valori bazate pe. Vă rugăm să le schimbați după nevoile dvs.

2. Apoi, apăsați Ctrl + Shift + Enter tastele împreună pentru a obține toate valorile potrivite într-o singură celulă, consultați captura de ecran:

Sfat:

Pentru a căuta și a returna toate valorile potrivite pe baza unor valori mai specifice într-o singură celulă, aplicați formula de mai jos și apăsați Ctrl + Shift + Enter chei.

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

Notă: Această formulă s-a aplicat cu succes numai în Excel 2016 și versiunile ulterioare. Dacă nu aveți Excel 2016, vă rugăm să vizualizați aici să-l doboare.

Mai multe articole Vlookup relative:

  • Vizualizați și returnați mai multe valori din lista derulantă
  • În Excel, cum ați putea să căutați și să returnați mai multe valori corespunzătoare dintr-o listă derulantă, ceea ce înseamnă că atunci când alegeți un articol din lista derulantă, toate valorile sale relative sunt afișate simultan, după cum se arată în următoarea captură de ecran. În acest articol, voi introduce soluția pas cu pas.
  • Vlookup va reveni în gol în loc de 0 sau N / A în Excel
  • În mod normal, atunci când aplicați funcția vlookup pentru a returna valoarea corespunzătoare, dacă celula potrivită este necompletată, aceasta va returna 0, iar dacă valoarea potrivită nu este găsită, veți primi o eroare # N / A valoare. În loc să afișați valoarea 0 sau # N / A, cum o puteți face să afișeze celula goală?
  • Vlookup Pentru a returna mai multe coloane din tabelul Excel
  • În foaia de lucru Excel, puteți aplica funcția Vlookup pentru a returna valoarea potrivită dintr-o coloană. Dar, uneori, poate fi necesar să extrageți valori potrivite din mai multe coloane, după cum se arată în următoarea captură de ecran. Cum ați putea obține valorile corespunzătoare în același timp din mai multe coloane utilizând funcția Vlookup?
  • Valori Vlookup pe mai multe foi de lucru
  • În Excel, putem aplica cu ușurință funcția vlookup pentru a returna valorile potrivite într-un singur tabel al unei foi de lucru. Dar, v-ați gândit vreodată că cum să căutați valoare pe mai multe foi de lucru? Presupunând că am următoarele trei foi de lucru cu o gamă de date și acum vreau să obțin o parte din valorile corespunzătoare pe baza criteriilor din aceste trei foi de lucru.
  • Super Formula Bar (editați cu ușurință mai multe linii de text și formulă); Layout de citire (citiți și editați cu ușurință un număr mare de celule); Lipiți la interval filtrat...
  • Merge celule / rânduri / coloane și păstrarea datelor; Conținut de celule divizate; Combinați rânduri duplicate și sumă / medie... Prevenirea celulelor duplicate; Comparați gamele...
  • Selectați Duplicat sau Unic Rânduri; Selectați Rânduri goale (toate celulele sunt goale); Super Find și Fuzzy Find în multe cărți de lucru; Selectare aleatorie ...
  • Copie exactă Mai multe celule fără modificarea referinței formulelor; Creați automat referințe la foi multiple; Introduceți gloanțe, Casete de selectare și multe altele ...
  • Formule favorite și inserare rapidă, Gama, Diagrame și Imagini; Criptați celulele cu parola; Creați o listă de corespondență și trimiteți e-mailuri ...
  • Extrageți textul, Adăugați text, eliminați după poziție, Eliminați spațiul; Creați și imprimați subtotaluri de paginare; Convertiți conținutul dintre celule și comentarii...
  • Super Filtru (salvați și aplicați scheme de filtrare altor foi); Sortare avansată după lună / săptămână / zi, frecvență și multe altele; Filtru special cu bold, italic ...
  • Combinați cărți de lucru și foi de lucru; Merge Tables pe baza coloanelor cheie; Împărțiți datele în mai multe foi; Conversia în loturi xls, xlsx și PDF...
  • Gruparea tabelului pivot după numărul săptămânii, ziua săptămânii și multe altele ... Afișați celulele deblocate, blocate prin diferite culori; Evidențiați celulele care au formulă / nume...
fila kte 201905
  • Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
  • Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
  • Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
fundul officetab
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