Γενική φόρμουλα
=XLOOKUP("*"&value&"*",lookup,results,,2)
Περίληψη
Για να χρησιμοποιήσετε το XLOOKUP για αντιστοίχιση τιμών που περιέχουν συγκεκριμένο κείμενο, μπορείτε να χρησιμοποιήσετε χαρακτήρες μπαλαντέρ και συνένωση. Στο παράδειγμα που εμφανίζεται, ο τύπος στο F5 είναι:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
όπου ο κωδικός (B5: B15) και η ποσότητα (C5: C15) ονομάζονται εύρη.
Εξήγηση
Η συνάρτηση XLOOKUP περιέχει ενσωματωμένη υποστήριξη για μπαλαντέρ, αλλά αυτή η λειτουργία πρέπει να ενεργοποιηθεί ρητά ρυθμίζοντας τη λειτουργία αντιστοίχισης στον αριθμό 2.
Στο παράδειγμα που εμφανίζεται, το XLOOKUP έχει ρυθμιστεί ώστε να ταιριάζει με την τιμή που έχει εισαχθεί στο κελί E5, η οποία μπορεί να εμφανίζεται οπουδήποτε στις τιμές αναζήτησης στο B5: B15. Ο τύπος στο F5 είναι:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, με συνδυασμούς αστερίσκων (*) εμπρός και πίσω
- lookup_array - ο κωδικός εύρους που ονομάζεται (B5: B15)
- return_array - η ονομαζόμενη ποσότητα εύρους (C5: C15)
- if_not_found - η συμβολοσειρά "no match"
- match_mode - παρέχεται ως 2 (αγώνας μπαλαντέρ)
- search_mode - δεν παρέχεται. Προεπιλογές στο 1 (πρώτο έως τελευταίο)
Για να γίνει αυτόματη αντιστοίχιση τύπου "περιέχει", ο αστερίσκος μπαλαντέρ (*) προπαρασκευάζεται και προσαρτάται στην τιμή στο κελί E5 με συνένωση:
"*"&E5&"*"
Μετά τη συνένωση, ο τύπος γίνεται:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
Το XLOOKUP εντοπίζει τον πρώτο αγώνα που περιέχει το "BCC" (050-BCC-123 στη σειρά 10) και επιστρέφει την αντίστοιχη τιμή από τον πίνακα επιστροφής, 50.
Λάβετε υπόψη ότι το XLOOKUP δεν είναι διάκριση πεζών-κεφαλαίων, η εισαγωγή του "bcc" στο E5 θα επιστρέψει το ίδιο αποτέλεσμα:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
Δείτε παρακάτω για μια επιλογή διαμόρφωσης του XLOOKUP για μια αντιστοίχιση με κεφαλαία.
Επιλογή VLOOKUP
Η φόρμουλα VLOOKUP υποστηρίζει επίσης χαρακτήρες μπαλαντέρ όταν ρυθμίζονται στην ακριβή αντιστοίχιση. Ο αντίστοιχος τύπος VLOOKUP για αυτό το παράδειγμα είναι:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Πλήρης εξήγηση εδώ.
Με ΑΝΑΖΗΤΗΣΗ και ΒΡΕΙΤΕ
Είναι επίσης δυνατό να χρησιμοποιήσετε τις συναρτήσεις SEARCH και FIND για να εκτελέσετε μια αντιστοίχιση τύπου "περιέχει" με το XLOOKUP. Για μια αντιστοίχιση χωρίς κεφαλαία (όπως το παραπάνω παράδειγμα), μπορείτε να χρησιμοποιήσετε την ΑΝΑΖΗΤΗΣΗ ως εξής:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
Για αντιστοιχία με πεζά γράμματα, μπορείτε να χρησιμοποιήσετε το FIND:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Και οι δύο παραπάνω επιλογές διευκολύνουν την επέκταση των κριτηρίων ώστε να περιλαμβάνονται και άλλες συνθήκες χρησιμοποιώντας λογική boolean.
Η λογική για το ISNUMBER + SEARCH εξηγείται εδώ.
Πολλαπλοί αγώνες
Εάν χρειάζεστε πολλαπλούς αγώνες, δείτε τη λειτουργία ΦΙΛΤΡΟ.
