Γενική φόρμουλα
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Περίληψη
Για να αντιστρέψετε ένα VLOOKUP - δηλαδή για να βρείτε την αρχική τιμή αναζήτησης χρησιμοποιώντας ένα αποτέλεσμα τύπου VLOOKUP - μπορείτε να χρησιμοποιήσετε μια δύσκολη φόρμουλα με βάση τη λειτουργία CHOOSE ή πιο απλούς τύπους που βασίζονται στο INDEX και MATCH ή XLOOKUP όπως εξηγείται παρακάτω. Στο παράδειγμα που παρουσιάζεται, ο τύπος στο H10 είναι:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Με αυτήν τη ρύθμιση, το VLOOKUP βρίσκει την επιλογή που σχετίζεται με κόστος 3000 και επιστρέφει το "C".
Σημείωση: αυτό είναι ένα πιο προηγμένο θέμα. Εάν μόλις ξεκινήσετε με το VLOOKUP, ξεκινήστε εδώ.
Εισαγωγή
Ένας βασικός περιορισμός του VLOOKUP είναι ότι μπορεί να αναζητήσει μόνο τιμές προς τα δεξιά. Με άλλα λόγια, η στήλη με τιμές αναζήτησης πρέπει να βρίσκεται αριστερά από τις τιμές που θέλετε να ανακτήσετε με το VLOOKUP. Ως αποτέλεσμα, με την τυπική διαμόρφωση, δεν υπάρχει τρόπος να χρησιμοποιήσετε το VLOOKUP για να "κοιτάξετε αριστερά" και να αντιστρέψετε την αρχική αναζήτηση.
Από την άποψη του VLOOKUP, μπορούμε να απεικονίσουμε το πρόβλημα ως εξής:
Η λύση που εξηγείται παρακάτω χρησιμοποιεί τη συνάρτηση CHOOSE για να αναδιατάξει τον πίνακα μέσα στο VLOOKUP.
Εξήγηση
Ξεκινώντας από την αρχή, ο τύπος στο H5 είναι ένας κανονικός τύπος VLOOKUP:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Χρησιμοποιώντας το G5 ως τιμή αναζήτησης ("C") και τα δεδομένα στο B5: D8 ως πίνακα πίνακα, το VLOOKUP εκτελεί αναζήτηση στις τιμές στη στήλη B και επιστρέφει την αντίστοιχη τιμή από τη στήλη 3 (στήλη D), 3000. Σημείωση Το μηδέν (0) παρέχεται ως το τελευταίο όρισμα για την εξαίρεση ενός ακριβούς αγώνα
Ο τύπος στο G10 τραβά απλά το αποτέλεσμα από το H5:
=H5 // 3000
Για να πραγματοποιήσετε μια αντίστροφη αναζήτηση, ο τύπος στο H10 είναι:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Το δύσκολο bit είναι η συνάρτηση CHOOSE, η οποία χρησιμοποιείται για την αναδιάταξη του πίνακα πίνακα έτσι ώστε το κόστος είναι η πρώτη στήλη και η επιλογή είναι η τελευταία:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Η συνάρτηση CHOOSE έχει σχεδιαστεί για να επιλέγει μια τιμή βάσει ενός αριθμητικού ευρετηρίου. Σε αυτήν την περίπτωση, παρέχουμε τρεις τιμές ευρετηρίου σε μια σταθερά πίνακα:
(3,2,1) // array constant
Με άλλα λόγια, ζητάμε τη στήλη 3, μετά τη στήλη 2 και μετά τη στήλη 1. Αυτό ακολουθείται από τις τρεις περιοχές που αντιπροσωπεύουν κάθε στήλη του πίνακα με τη σειρά που εμφανίζονται στο φύλλο εργασίας.
Με αυτήν τη διαμόρφωση, το CHOOSE επιστρέφει και τις τρεις στήλες σε ένα μόνο 2D πίνακα όπως αυτό:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Εάν απεικονίσουμε αυτόν τον πίνακα ως πίνακα στο φύλλο εργασίας, έχουμε:
Σημείωση: οι επικεφαλίδες δεν αποτελούν μέρος του πίνακα και εμφανίζονται εδώ μόνο για λόγους σαφήνειας.
Αποτελεσματικά, έχουμε αλλάξει τις στήλες 1 και 3. Ο αναδιοργανωμένος πίνακας επιστρέφεται απευθείας στο VLOOKUP, το οποίο ταιριάζει με το 3000 και επιστρέφει την αντίστοιχη τιμή από τη στήλη 3, "C".
Με INDEX και MATCH
Η παραπάνω λύση λειτουργεί καλά, αλλά είναι δύσκολο να προταθεί, καθώς οι περισσότεροι χρήστες δεν θα καταλάβουν πώς λειτουργεί ο τύπος. Μια καλύτερη λύση είναι το INDEX και MATCH, χρησιμοποιώντας έναν τύπο όπως αυτό:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Εδώ, η συνάρτηση MATCH βρίσκει την τιμή 3000 σε D5: D8 και επιστρέφει τη θέση της, 3:
MATCH(G10,D5:D8,0) // returns 3
Σημείωση: Το MATCH έχει διαμορφωθεί για ακριβή αντιστοίχιση ρυθμίζοντας το τελευταίο όρισμα στο μηδέν (0)
Το MATCH επιστρέφει ένα αποτέλεσμα απευθείας στο INDEX ως αριθμό σειράς, οπότε ο τύπος γίνεται:
=INDEX(B5:B8,3) // returns "C"
και το INDEX επιστρέφει την τιμή από την τρίτη σειρά του B5: B8, "C".
Αυτός ο τύπος δείχνει πώς τα INDEX και MATCH μπορούν να είναι πιο ευέλικτα από το VLOOKUP.
Με XLOOKUP
Το XLOOKUP παρέχει επίσης μια πολύ καλή λύση. Ο ισοδύναμος τύπος είναι:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Με μια τιμή αναζήτησης από το G10 (3000), έναν πίνακα al ookup D5: D8 (κόστος) και έναν πίνακα αποτελεσμάτων B5: B8 (επιλογές), το XLOOKUP εντοπίζει το 3000 σε πίνακα αναζήτησης και επιστρέφει το αντίστοιχο στοιχείο από τον πίνακα αποτελεσμάτων, "ΝΤΟ". Επειδή το XLOOKUP εκτελεί μια ακριβή αντιστοίχιση από προεπιλογή, δεν χρειάζεται να ρυθμίσετε τη λειτουργία αντιστοίχισης ρητά.
