From the course: Excel: Advanced Formulas and Functions

XLOOKUP with wildcards

From the course: Excel: Advanced Formulas and Functions

Start my 1-month free trial

XLOOKUP with wildcards

“

- Let's look at XLOOKUP using wildcards. In columns F and G, we've got Projects and Codes. In column C, we want to put lookup values that will retrieve the appropriate project, but we're going to to use wildcards and I'm going to put this data into a table. The cursor is in the data set. Format as Table. Let's grab this blue. Table has headers. Okay. Great. Get rid of the filter buttons. Alright. I want to look up the project where the code has AA in the middle. Equals, XLOOKUP Double click. Lookup what? Lookup value. The AA. Comma, look up array. I wanted to look in this code column, comma, return, return the project name, comma, if not found, I'm not worried about that. Comma match mode. This is where we put a two for wild card character match. And I'm going to close parentheses and enter. Hey, we've got N/A's, of course. To get XLOOKUP to see the code that has AA somewhere in the middle, I'm going to put the asterisk, and then asterisk, enter. See? We got red. But now let's go back to this one and look. Let's get rid of this asterisk on the end. Enter. Now it goes back to N/A. Why? Because now we're telling it XLOOKUP to look up the code that ends with AA. That's not what we wanted. We want it somewhere in the middle. Good. Now the one that ends with R, I've already kind of told you that, right? So we want asterisk and R. Enter. Exactly the one that ends with an R is the purple project. G in the fourth position. Instead of the asterisk we have to use dollar sign, dollar sign, dollar sign, and then G. But watch this. Okay, enter. We have N/A because we don't want the one that has G in the fourth and final position. We have to go back and put in our asterisk, enter. There we go. Silver. Now, ends with a question mark and starts with three. Okay, starts with three and then asterisk. And we have to use a tilde before I wild card so that Excel knows when I use this question mark that I'm not using it as a wild card. So the tilde and question mark, enter. Lemon. And now just plain ends with question mark. Watch what we get. I'm going to go tilde and question mark. Enter. That didn't do anything because I am looking for just a question mark. I have to go back, put in the asterisk, enter. Charcoal. But now that's the first one in the list that it came to. It did not get down to pink and lemon. Now we want the one that ends with D, question mark. Again, asterisk, D, and then the tilde and question mark and enter. There we have it, charcoal. And one more thing before we get out of here. I'm going to go back to the asterisk R and I'm going to make this a lowercase R, enter. We still have purple and that's to let you know that these are not case sensitive. And there you have it. XLOOKUP with wild cards.

Contents