Γενική φόρμουλα
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Περίληψη
Για να αναζητήσετε τιμές με INDEX και MATCH, χρησιμοποιώντας πολλαπλά κριτήρια, μπορείτε να χρησιμοποιήσετε έναν τύπο πίνακα. Στο παράδειγμα που εμφανίζεται, ο τύπος στο H8 είναι:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Σημείωση: πρόκειται για έναν τύπο πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter, εκτός από το Excel 365.
Εξήγηση
Αυτή είναι μια πιο προηγμένη φόρμουλα. Για βασικά, ανατρέξτε στην ενότητα Πώς να χρησιμοποιήσετε το INDEX και το MATCH.
Κανονικά, ένας τύπος INDEX MATCH έχει διαμορφωθεί με MATCH σετ ώστε να κοιτάζει μέσα από ένα εύρος μιας στήλης και να παρέχει μια αντιστοίχιση βάσει συγκεκριμένων κριτηρίων. Χωρίς συνένωση τιμών σε μια βοηθητική στήλη ή στον ίδιο τον τύπο, δεν υπάρχει τρόπος να παρέχονται περισσότερα από ένα κριτήρια.
Αυτός ο τύπος λειτουργεί γύρω από αυτόν τον περιορισμό χρησιμοποιώντας λογική boolean για να δημιουργήσει μια σειρά από αυτά και μηδενικά για να αντιπροσωπεύσει σειρές που ταιριάζουν και στα 3 κριτήρια και στη συνέχεια χρησιμοποιώντας MATCH για να ταιριάξει με το πρώτο 1 που βρέθηκε. Ο προσωρινός πίνακας αυτών και μηδενικών δημιουργείται με αυτό το απόσπασμα:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Εδώ συγκρίνουμε το αντικείμενο σε H5 με όλα τα είδη, το μέγεθος σε H6 με όλα τα μεγέθη και το χρώμα σε H7 με όλα τα χρώματα. Το αρχικό αποτέλεσμα είναι τρεις συστοιχίες ΑΛΗΘΕΙΑΣ / ΛΑΘΟΣ αποτελεσμάτων ως εξής:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Συμβουλή: χρησιμοποιήστε το F9 για να δείτε αυτά τα αποτελέσματα. Απλώς επιλέξτε μια παράσταση στη γραμμή τύπων και πατήστε F9.
Η μαθηματική λειτουργία (πολλαπλασιασμός) μετατρέπει τις τιμές TRUE FALSE σε 1s και 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Μετά τον πολλαπλασιασμό, έχουμε έναν ενιαίο πίνακα όπως αυτό:
(0;0;1;0;0;0;0)
η οποία τροφοδοτείται στη λειτουργία MATCH ως πίνακας αναζήτησης, με τιμή αναζήτησης 1:
MATCH(1,(0;0;1;0;0;0;0))
Σε αυτό το σημείο, ο τύπος είναι ένας τυπικός τύπος INDEX MATCH. Η συνάρτηση MATCH επιστρέφει 3 στο INDEX:
=INDEX(E5:E11,3)
και το INDEX επιστρέφει ένα τελικό αποτέλεσμα 17,00 $.
Οπτικοποίηση συστοιχίας
Οι πίνακες που εξηγούνται παραπάνω μπορεί να είναι δύσκολο να απεικονιστούν. Η παρακάτω εικόνα δείχνει τη βασική ιδέα. Οι στήλες B, C και D αντιστοιχούν στα δεδομένα του παραδείγματος. Η στήλη F δημιουργείται πολλαπλασιάζοντας τις τρεις στήλες μαζί. Είναι ο πίνακας που παραδόθηκε στο MATCH.
Έκδοση χωρίς συστοιχία
Είναι δυνατή η προσθήκη ενός άλλου INDEX σε αυτόν τον τύπο, αποφεύγοντας την ανάγκη εισαγωγής ως τύπος πίνακα με έλεγχο + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Η συνάρτηση INDEX μπορεί να χειριστεί τους πίνακες εγγενώς, οπότε ο δεύτερος INDEX προστίθεται μόνο για να "πιάσει" τον πίνακα που δημιουργήθηκε με τη λειτουργία λογικής boolean και να επιστρέψει τον ίδιο πίνακα ξανά στο MATCH. Για να γίνει αυτό, το INDEX διαμορφώνεται με μηδενικές σειρές και μία στήλη. Το κόλπο μηδενικής σειράς κάνει το INDEX να επιστρέψει τη στήλη 1 από τον πίνακα (ο οποίος είναι ήδη μία στήλη ούτως ή άλλως).
Γιατί θα θέλατε την έκδοση χωρίς συστοιχία; Μερικές φορές, οι άνθρωποι ξεχνούν να εισαγάγουν έναν τύπο πίνακα με τον έλεγχο + shift + enter και ο τύπος επιστρέφει ένα λανθασμένο αποτέλεσμα. Έτσι, ένας τύπος χωρίς συστοιχία είναι πιο «αλεξίσφαιρος». Ωστόσο, η αντιστάθμιση είναι ένας πιο περίπλοκος τύπος.
Σημείωση: Στο Excel 365, δεν είναι απαραίτητο να εισαγάγετε τύπους πίνακα με έναν ειδικό τρόπο.
