Για όσους από εσάς γνωρίζουν καλά το Excel , πιθανότατα είστε πολύ εξοικειωμένοι με τη συνάρτηση VLOOKUP^(VLOOKUP) . Η συνάρτηση VLOOKUP^(VLOOKUP) χρησιμοποιείται για την εύρεση μιας τιμής σε διαφορετικό κελί με βάση κάποιο αντίστοιχο κείμενο στην ίδια σειρά.

Εάν είστε ακόμα νέος στη λειτουργία VLOOKUP , μπορείτε να δείτε την προηγούμενη ανάρτησή μου σχετικά με τον τρόπο χρήσης του VLOOKUP στο Excel^{(how to use VLOOKUP in Excel)} .

Όσο ισχυρό και αν είναι, το VLOOKUP έχει έναν περιορισμό στον τρόπο με τον οποίο πρέπει να δομηθεί ο αντίστοιχος πίνακας αναφοράς για να λειτουργήσει ο τύπος.

Αυτό το άρθρο θα σας δείξει τον περιορισμό όπου δεν μπορεί να χρησιμοποιηθεί το VLOOKUP και θα εισαγάγει μια άλλη συνάρτηση στο ^(VLOOKUP)Excel που ονομάζεται INDEX-MATCH που μπορεί να λύσει το πρόβλημα.

ΑΝΤΙΣΤΟΙΧΙΣΗ ΕΥΡΕΤΗΡΙΟΥ Παράδειγμα Excel

Χρησιμοποιώντας το ακόλουθο παράδειγμα υπολογιστικού φύλλου Excel , έχουμε μια λίστα με το όνομα του κατόχου του αυτοκινήτου και το όνομα του αυτοκινήτου. Σε αυτό το παράδειγμα, θα προσπαθήσουμε να πάρουμε το  αναγνωριστικό αυτοκινήτου^{(Car ID)} με βάση το μοντέλο αυτοκινήτου^{(Car Model)} που αναφέρεται σε πολλούς κατόχους όπως φαίνεται παρακάτω:

Σε ένα ξεχωριστό φύλλο που ονομάζεται CarType , έχουμε μια απλή βάση δεδομένων αυτοκινήτου με το  αναγνωριστικό^(ID) , το μοντέλο αυτοκινήτου^{(Car Model)} και το χρώμα^(Color) .

Με αυτήν τη ρύθμιση πίνακα, η  συνάρτηση VLOOKUP^(VLOOKUP) μπορεί να λειτουργήσει μόνο εάν τα δεδομένα που θέλουμε να ανακτήσουμε βρίσκονται στη στήλη στα δεξιά αυτού που προσπαθούμε να αντιστοιχίσουμε ( πεδίο μοντέλου αυτοκινήτου^{(Car Model )} ).

Με άλλα λόγια, με αυτήν τη δομή πίνακα, καθώς προσπαθούμε να την αντιστοιχίσουμε με βάση το μοντέλο αυτοκινήτου^{(Car Model)} , οι μόνες πληροφορίες που μπορούμε να πάρουμε είναι το Χρώμα^{(Color )} (Όχι το αναγνωριστικό^(ID) καθώς η στήλη ID  βρίσκεται στα αριστερά της  στήλης Μοντέλο αυτοκινήτου^{(Car Model )} .)

Αυτό συμβαίνει επειδή με το VLOOKUP , η τιμή αναζήτησης πρέπει να εμφανίζεται στην πρώτη στήλη και οι στήλες αναζήτησης πρέπει να βρίσκονται στα δεξιά. Καμία από αυτές τις προϋποθέσεις δεν πληρούται στο παράδειγμά μας.

Τα καλά νέα είναι ότι το INDEX-MATCH^{(INDEX-MATCH)}  θα μπορέσει να μας βοηθήσει να το πετύχουμε. Στην πράξη, αυτό στην πραγματικότητα συνδυάζει δύο συναρτήσεις του Excel που μπορούν να λειτουργήσουν μεμονωμένα: συνάρτηση INDEX και συνάρτηση MATCH .

Ωστόσο, για τους σκοπούς αυτού του άρθρου, θα μιλήσουμε μόνο για τον συνδυασμό των δύο με στόχο την αναπαραγωγή της λειτουργίας του VLOOKUP .

Η φόρμουλα μπορεί να φαίνεται να είναι λίγο μεγάλη και τρομακτική στην αρχή. Ωστόσο, αφού το έχετε χρησιμοποιήσει αρκετές φορές, θα μάθετε τη σύνταξη από έξω.

Αυτός είναι ο πλήρης τύπος στο παράδειγμά μας:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Εδώ είναι η ανάλυση για κάθε ενότητα

=INDEX( – Το “=” υποδεικνύει την αρχή του τύπου στο κελί και το INDEX είναι το πρώτο μέρος της συνάρτησης Excel που χρησιμοποιούμε.

CarType!$A$2:$A$5 – οι στήλες στο φύλλο CarType  όπου περιέχονται τα δεδομένα που θα θέλαμε να ανακτήσουμε. Σε αυτό το παράδειγμα, το αναγνωριστικό^(ID)  κάθε μοντέλου αυτοκινήτου.^{(Car Model.)}

MATCH( – Το δεύτερο μέρος της συνάρτησης Excel που χρησιμοποιούμε.

B4 – Το κελί που περιέχει κείμενο αναζήτησης που χρησιμοποιούμε ( Model Car^{(Car Model)} ) .

CarType!$B$2:$B$5 – Οι στήλες στο φύλλο CarType   με τα δεδομένα που θα χρησιμοποιήσουμε για να ταιριάξουμε με το κείμενο αναζήτησης.

0)) – Για να υποδείξετε ότι το κείμενο αναζήτησης πρέπει να ταιριάζει ακριβώς με το κείμενο στην αντίστοιχη στήλη (δηλ CarType!$B$2:$B$5 ). Εάν δεν βρεθεί η ακριβής αντιστοίχιση, ο τύπος επιστρέφει #N/A .

Σημείωση: θυμηθείτε τη διπλή αγκύλη κλεισίματος στο τέλος αυτής της συνάρτησης "))" και τα κόμματα μεταξύ των ορισμάτων.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

Προσωπικά έχω απομακρυνθεί από το VLOOKUP και τώρα χρησιμοποιώ το INDEX-MATCH καθώς μπορεί να κάνει περισσότερα από το VLOOKUP .

Οι λειτουργίες INDEX-MATCH έχουν επίσης άλλα πλεονεκτήματα σε σύγκριση με το VLOOKUP :

1. Ταχύτεροι Υπολογισμοί^{(Faster Calculations)}

Όταν εργαζόμαστε με μεγάλα σύνολα δεδομένων όπου ο ίδιος ο υπολογισμός μπορεί να διαρκέσει πολύ λόγω πολλών συναρτήσεων VLOOKUP^(VLOOKUP) , θα διαπιστώσετε ότι μόλις αντικαταστήσετε όλους αυτούς τους τύπους με το INDEX-MATCH^{(INDEX-MATCH)} , ο συνολικός υπολογισμός θα υπολογιστεί πιο γρήγορα.

1. Δεν χρειάζεται να μετράτε σχετικές στήλες^{(No Need to Count Relative Columns)}

Εάν ο πίνακας αναφοράς έχει το βασικό κείμενο που θέλουμε να αναζητήσουμε στη στήλη C και τα δεδομένα που πρέπει να πάρουμε είναι στη στήλη AQ , θα πρέπει να γνωρίζουμε/μετράμε πόσες στήλες βρίσκονται μεταξύ της στήλης C και της στήλης AQ όταν χρησιμοποιούμε το VLOOKUP^(VLOOKUP) .

Με τις  συναρτήσεις INDEX-MATCH , μπορούμε να επιλέξουμε απευθείας τη στήλη ευρετηρίου (π.χ. στήλη AQ) όπου πρέπει να λάβουμε τα δεδομένα και να επιλέξουμε τη στήλη που θα αντιστοιχιστεί (δηλ. στήλη C).

1. Φαίνεται πιο περίπλοκο^{(It Looks More Complicated)}

Το VLOOKUP^(VLOOKUP) είναι αρκετά συνηθισμένο στις μέρες μας, αλλά δεν γνωρίζουν πολλοί για τη χρήση των συναρτήσεων INDEX-MATCH μαζί.

Η μεγαλύτερη συμβολοσειρά στη συνάρτηση INDEX-MATCH σας βοηθά να φαίνεστε σαν ειδικός στο χειρισμό πολύπλοκων και προηγμένων συναρτήσεων του Excel . Απολαμβάνω!

When to Use Index-Match Instead of VLOOKUP in Excel

For those of уou who are well-versed in Excel, you are most likely very familiаr with the VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.

If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.

As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.

This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.

INDEX MATCH Excel Example

Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:

On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.

With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).

In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)

This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.

The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.

However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.

The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.

This is the full formula in our example:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Here is the breakdown for each section

=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.

CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.

MATCH( – The second part of the Excel function that we are using.

B4 – The cell that contain search text that we are using (Car Model).

CarType!$B$2:$B$5 – The columns on sheet CarType  with the data which we will use to match against the search text.

0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.

Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.

Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.

The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:

1. Faster Calculations

When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.

1. No Need to Count Relative Columns

If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.

With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).

1. It Looks More Complicated

VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.

The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!

Related posts

• Τρόπος διόρθωσης σφαλμάτων #N/A σε τύπους Excel όπως το VLOOKUP

• Πώς να διαγράψετε κενές γραμμές στο Excel

• Πώς να χρησιμοποιήσετε τη δυνατότητα Speak Cells του Excel

• Πώς να εισαγάγετε ένα φύλλο εργασίας του Excel σε ένα έγγραφο του Word

• Πώς να χρησιμοποιήσετε την ανάλυση What-If του Excel

• Πώς να υπολογίσετε το Z-Score στο Excel

• Πώς να δημιουργήσετε ένα γράφημα πίτας στο Excel

• Πώς να αφαιρέσετε διπλότυπες σειρές στο Excel

• Βασική ταξινόμηση δεδομένων μίας στήλης και πολλών στηλών σε υπολογιστικά φύλλα του Excel

• Πώς να γράψετε έναν τύπο/δήλωση IF στο Excel

• Πώς να χρησιμοποιήσετε το Flash Fill In Excel

• 2 τρόποι χρήσης της συνάρτησης Transpose του Excel

• Πώς να εισαγάγετε CSV ή TSV σε ένα φύλλο εργασίας του Excel

• Χρησιμοποιήστε ονόματα δυναμικού εύρους στο Excel για ευέλικτα αναπτυσσόμενα

• 3 τρόποι για να χωρίσετε ένα κελί στο Excel

• 10 Συμβουλές και κόλπα του Excel για το 2019

• Διατηρήστε τις αναφορές κελιών κατά την αντιγραφή ενός τύπου στο Excel

• Πώς να αλφαβητίσετε στο Excel

• Πώς να δημιουργήσετε πολλαπλές συνδεδεμένες αναπτυσσόμενες λίστες στο Excel

• Χρησιμοποιήστε το πληκτρολόγιο για να αλλάξετε το ύψος γραμμής και το πλάτος στήλης στο Excel