Μετάβαση στο κύριο περιεχόμενο

Αναζητήστε και επιστρέψτε πολλαπλές τιμές βάσει ενός ή πολλαπλών κριτηρίων

Κανονικά, μπορείτε να χρησιμοποιήσετε τη συνάρτηση Vlookup για να λάβετε την πρώτη αντίστοιχη τιμή, αλλά, μερικές φορές, θέλετε να επιστρέψετε όλες τις εγγραφές που ταιριάζουν με βάση ένα συγκεκριμένο κριτήριο. Αυτό το άρθρο, θα μιλήσω για το πώς να βλέπω και να επιστρέφω όλες τις αντίστοιχες τιμές κάθετα, οριζόντια ή σε ένα μόνο κελί.

Vlookup και επιστροφή όλων των αντίστοιχων τιμών κάθετα

Vlookup και επιστροφή όλων των αντίστοιχων τιμών οριζόντια

Vlookup και επιστροφή όλων των αντίστοιχων τιμών σε ένα κελί

Vlookup και επιστροφή όλων των αντίστοιχων τιμών κάθετα

Για να επιστρέψετε όλες τις αντίστοιχες τιμές κάθετα με βάση ένα συγκεκριμένο κριτήριο, εφαρμόστε τον ακόλουθο τύπο πίνακα:

1. Εισαγάγετε ή αντιγράψτε αυτόν τον τύπο σε ένα κενό κελί όπου θέλετε να εξάγετε το αποτέλεσμα:

=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: Στον παραπάνω τύπο, C2: C20 είναι η στήλη περιέχει την εγγραφή που θέλετε να επιστρέψετε. A2: A20 είναι η στήλη περιέχει το κριτήριο? και E2 είναι το συγκεκριμένο κριτήριο στο οποίο θέλετε να επιστρέψετε τιμές βάσει. Αλλάξτε τα ανάλογα με τις ανάγκες σας.

2. Στη συνέχεια, πατήστε Ctrl + Shift + Εισαγωγή πλήκτρα μαζί για να λάβετε την πρώτη τιμή και, στη συνέχεια, σύρετε τη λαβή πλήρωσης προς τα κάτω για να λάβετε όλες τις αντίστοιχες εγγραφές όπως χρειάζεστε, δείτε το στιγμιότυπο οθόνης:

Συμβουλές:

Για να κάνετε Vlookup και να επιστρέψετε όλες τις τιμές που ταιριάζουν με βάση πιο συγκεκριμένες τιμές κάθετα, εφαρμόστε τον παρακάτω τύπο και πατήστε Ctrl + Shift + Εισαγωγή κλειδιά.

=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 και επιστροφή όλων των αντίστοιχων τιμών οριζόντια

Εάν θέλετε να εμφανίζονται οι τιμές αντιστοίχισης σε οριζόντια σειρά, ο παρακάτω τύπος πίνακα μπορεί να σας βοηθήσει.

1. Εισαγάγετε ή αντιγράψτε αυτόν τον τύπο σε ένα κενό κελί όπου θέλετε να εξάγετε το αποτέλεσμα:

=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: Στον παραπάνω τύπο, C2: C20 είναι η στήλη περιέχει την εγγραφή που θέλετε να επιστρέψετε. A2: A20 είναι η στήλη περιέχει το κριτήριο? και F1 είναι το συγκεκριμένο κριτήριο στο οποίο θέλετε να επιστρέψετε τιμές βάσει. Αλλάξτε τα ανάλογα με τις ανάγκες σας.

2. Στη συνέχεια, πατήστε Ctrl + Shift + Εισαγωγή πλήκτρα μαζί για να λάβετε την πρώτη τιμή και, στη συνέχεια, σύρετε τη λαβή πλήρωσης δεξιά για να λάβετε όλες τις αντίστοιχες εγγραφές όπως χρειάζεστε, δείτε το στιγμιότυπο οθόνης:

Συμβουλές:

Για να κάνετε Vlookup και να επιστρέψετε όλες τις τιμές που ταιριάζουν με βάση πιο συγκεκριμένες τιμές οριζόντια, εφαρμόστε τον παρακάτω τύπο και πατήστε Ctrl + Shift + Εισαγωγή κλειδιά.

=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 και επιστροφή όλων των αντίστοιχων τιμών σε ένα κελί

Για να δείτε και να επιστρέψετε όλες τις αντίστοιχες τιμές σε ένα κελί, θα πρέπει να εφαρμόσετε τον ακόλουθο τύπο πίνακα.

1. Εισαγάγετε ή αντιγράψτε τον παρακάτω τύπο σε ένα κενό κελί:

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

Note: Στον παραπάνω τύπο, C2: C20 είναι η στήλη περιέχει την εγγραφή που θέλετε να επιστρέψετε. A2: A20 είναι η στήλη περιέχει το κριτήριο? και F1 είναι το συγκεκριμένο κριτήριο στο οποίο θέλετε να επιστρέψετε τιμές βάσει. Αλλάξτε τα ανάλογα με τις ανάγκες σας.

2. Στη συνέχεια, πατήστε Ctrl + Shift + Εισαγωγή πλήκτρα μαζί για να συγκεντρώσετε όλες τις αντίστοιχες τιμές σε ένα κελί, δείτε το στιγμιότυπο οθόνης:

Συμβουλές:

Για να κάνετε Vlookup και να επιστρέψετε όλες τις τιμές που ταιριάζουν με βάση πιο συγκεκριμένες τιμές σε ένα μόνο κελί, εφαρμόστε τον παρακάτω τύπο και πατήστε Ctrl + Shift + Εισαγωγή κλειδιά.

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

Σημείωση: Αυτός ο τύπος εφαρμόστηκε με επιτυχία μόνο στο Excel 2016 και σε νεότερες εκδόσεις. Εάν δεν έχετε το Excel 2016, δείτε εδώ για να το κατεβάσεις.

Σχετικά άρθρα Vlookup:

  • Vlookup και Return Data Matching μεταξύ δύο τιμών στο Excel
  • Στο Excel, μπορούμε να εφαρμόσουμε τη συνήθη συνάρτηση Vlookup για να λάβουμε την αντίστοιχη τιμή με βάση δεδομένα δεδομένα. Αλλά, μερικές φορές, θέλουμε να κοιτάξουμε και να επιστρέψουμε την αντίστοιχη τιμή μεταξύ δύο τιμών, πώς θα μπορούσατε να αντιμετωπίσετε αυτήν την εργασία στο Excel;
  • Vlookup και επιστροφή πολλαπλών τιμών από την αναπτυσσόμενη λίστα
  • Στο Excel, πώς θα μπορούσατε να προβάλετε και να επιστρέψετε πολλές αντίστοιχες τιμές από μια αναπτυσσόμενη λίστα, πράγμα που σημαίνει ότι όταν επιλέγετε ένα στοιχείο από την αναπτυσσόμενη λίστα, όλες οι σχετικές τιμές εμφανίζονται ταυτόχρονα όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης. Αυτό το άρθρο, θα παρουσιάσω τη λύση βήμα προς βήμα.
  • Vlookup για επιστροφή κενού αντί για 0 ​​ή N / A στο Excel
  • Κανονικά, όταν εφαρμόζετε τη συνάρτηση vlookup για να επιστρέψετε την αντίστοιχη τιμή, εάν το κελί που ταιριάζει είναι κενό, θα επιστρέψει 0 και αν δεν βρεθεί η τιμή που ταιριάζει, θα λάβετε μια τιμή σφάλματος # Δ / Α. Αντί να εμφανίζεται η τιμή 0 ή # N / A, πώς μπορείτε να την εμφανίσετε κενό κελί;
  • Vlookup για επιστροφή πολλαπλών στηλών από τον πίνακα Excel
  • Στο φύλλο εργασίας του Excel, μπορείτε να εφαρμόσετε τη συνάρτηση Vlookup για να επιστρέψετε την αντίστοιχη τιμή από μία στήλη. Όμως, μερικές φορές, ίσως χρειαστεί να εξαγάγετε αντιστοιχισμένες τιμές από πολλές στήλες, όπως φαίνεται παρακάτω. Πώς θα μπορούσατε να λάβετε τις αντίστοιχες τιμές ταυτόχρονα από πολλές στήλες χρησιμοποιώντας τη συνάρτηση Vlookup;
  • Τιμές Vlookup σε πολλά φύλλα εργασίας
  • Στο excel, μπορούμε εύκολα να εφαρμόσουμε τη συνάρτηση vlookup για να επιστρέψουμε τις αντίστοιχες τιμές σε έναν πίνακα ενός φύλλου εργασίας. Όμως, έχετε σκεφτεί ποτέ πώς να κοιτάξετε την τιμή σε πολλά φύλλα εργασίας; Υποθέτοντας ότι έχω τα ακόλουθα τρία φύλλα εργασίας με εύρος δεδομένων, και τώρα, θέλω να λάβω μέρος των αντίστοιχων τιμών με βάση τα κριτήρια από αυτά τα τρία φύλλα εργασίας.
  • Super Formula Bar (επεξεργαστείτε εύκολα πολλές γραμμές κειμένου και τύπου). Διάταξη ανάγνωσης (εύκολη ανάγνωση και επεξεργασία μεγάλου αριθμού κελιών). Επικόλληση σε φιλτραρισμένο εύρος...
  • Συγχώνευση κελιών / σειρών / στηλών και τήρηση δεδομένων · Περιεχόμενο διαχωρισμού κελιών Συνδυάστε διπλές σειρές και άθροισμα / μέσος όρος... Αποτροπή διπλών κυττάρων; Συγκρίνετε τα εύρη...
  • Επιλέξτε Διπλότυπο ή Μοναδικό Σειρές; Επιλέξτε Κενές σειρές (όλα τα κελιά είναι κενά). Σούπερ εύρεση και ασαφής εύρεση σε πολλά βιβλία εργασίας. Τυχαία επιλογή ...
  • Ακριβές αντίγραφο Πολλαπλά κελιά χωρίς αλλαγή της αναφοράς τύπου. Αυτόματη δημιουργία αναφορών σε πολλαπλά φύλλα? Εισαγωγή κουκκίδων, Πλαίσια ελέγχου και άλλα ...
  • Αγαπημένα και γρήγορη εισαγωγή τύπων, Σειρά, Διαγράμματα και Εικόνες; Κρυπτογράφηση κυττάρων με κωδικό πρόσβασης Δημιουργία λίστας αλληλογραφίας και στείλτε email ...
  • Εξαγωγή κειμένου, Προσθήκη κειμένου, Κατάργηση κατά θέση, Αφαιρέστε το διάστημα; Δημιουργία και εκτύπωση υποσύνολων σελιδοποίησης. Μετατροπή περιεχομένου και σχολίων μεταξύ κελιών...
  • Σούπερ φίλτρο (αποθηκεύστε και εφαρμόστε σχήματα φίλτρων σε άλλα φύλλα). Προηγμένη ταξινόμηση ανά μήνα / εβδομάδα / ημέρα, συχνότητα και άλλα. Ειδικό φίλτρο με έντονη, πλάγια ...
  • Συνδυάστε βιβλία εργασίας και φύλλα εργασίας; Συγχώνευση πινάκων βάσει βασικών στηλών. Διαχωρίστε τα δεδομένα σε πολλά φύλλα; Μαζική μετατροπή xls, xlsx και PDF...
  • Ομαδοποίηση συγκεντρωτικού πίνακα κατά αριθμός εβδομάδας, ημέρα εβδομάδας και πολλά άλλα ... Εμφάνιση ξεκλειδωμένων, κλειδωμένων κελιών με διαφορετικά χρώματα. Επισημάνετε τα κελιά που έχουν τύπο / όνομα...
kte καρτέλα 201905
  • Ενεργοποίηση επεξεργασίας και ανάγνωσης καρτελών σε Word, Excel, PowerPoint, Publisher, Access, Visio και Project.
  • Ανοίξτε και δημιουργήστε πολλά έγγραφα σε νέες καρτέλες του ίδιου παραθύρου και όχι σε νέα παράθυρα.
  • Αυξάνει την παραγωγικότητά σας κατά 50% και μειώνει εκατοντάδες κλικ του ποντικιού για εσάς κάθε μέρα!
κάτω μέρος γραφείου
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