Introduction to VLOOKUP and XLOOKUP
Before we dive into troubleshooting, let’s briefly review what VLOOKUP and XLOOKUP are and how they are used in Excel.
VLOOKUP, which stands for ‘Vertical Lookup,’ is a function that searches for a value in the first column of a table and returns a value in the same row from a specified column. Its syntax is:
`=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`
XLOOKUP, introduced in Excel for Microsoft 365, is a more versatile and powerful function that can search in any direction and return a value from any column or row. Its syntax is:
`=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`
Troubleshooting VLOOKUP
Ensure Correct Lookup Value
– Problem: VLOOKUP is not returning the correct value.
– Solution: Check if the `lookup_value` is accurate and exists in the first column of the `table_array`.
2. Verify Table Array Reference
– Problem: #REF! error.
– Solution: Ensure the `table_array` is correctly referenced, and the `col_index_num` does not exceed the number of columns in the `table_array`.
3. Check Column Index Number
– Problem: Incorrect results are returned.
– Solution: Confirm that the `col_index_num` corresponds to the correct column from which you want to retrieve the value.
4. Range Lookup Argument
– Problem: Approximate match instead of an exact match.
– Solution: Set the `range_lookup` argument to FALSE for an exact match.
Example of VLOOKUP Troubleshooting
| A | B | C | D |
|—|—|—|—|
| 1 | Product ID | Product Name | Price |
| 2 | 101 | Apples | $1.00 |
| 3 | 102 | Bananas | $0.50 |
| 4 | 103 | Cherries | $3.00 |
If you use `=VLOOKUP(102, A1:C4, 3, TRUE)`, it might return an incorrect price if the Product IDs are not sorted. To fix this, use `=VLOOKUP(102, A1:C4, 3, FALSE)` for an exact match.
Troubleshooting XLOOKUP
Correct Lookup and Return Arrays
– Problem: #VALUE! or #N/A error.
– Solution: Ensure `lookup_array` and `return_array` are correctly specified and have the same dimensions.
2. Handling Not Found Values
– Problem: #N/A error when the value is not found.
– Solution: Use the `[if_not_found]` argument to specify a custom message or value when the `lookup_value` is not found.
3. Match Mode and Search Mode
– Problem: Not finding the correct match type.
– Solution: Use the `[match_mode]` argument to specify the type of match (exact match, wildcard, etc.) and `[search_mode]` to define the search direction.
Example of XLOOKUP Troubleshooting
| A | B | C |
|—|—|—|
| 1 | Product Name | Price |
| 2 | Apples | $1.00 |
| 3 | Bananas | $0.50 |
| 4 | Cherries | $3.00 |
If you use `=XLOOKUP(“Banana”, A2:A4, B2:B4)`, it will return #N/A because of the typo. Correct the spelling to “Bananas” or use the `[if_not_found]` argument like `=XLOOKUP(“Banana”, A2:A4, B2:B4, “Not Found”)` to handle errors gracefully.
Conclusion
Mastering the VLOOKUP and XLOOKUP functions in Excel can significantly improve your data analysis capabilities. By understanding common issues and learning how to troubleshoot them, you can ensure that your lookup functions are accurate and efficient. Remember to check your lookup values, verify table array references, and understand the importance of match types. With these tips, you’ll be well on your way to becoming an Excel lookup function expert.
Live Excel Support Transcript Example:
Below is a live transcript of one of our Excel experts helping a customer with VLOOKUP and XLOOKUP:
Full Conversation:
Customer: Hey, Jordan. How are you? Jordan, are you there? Hello? Hey, Jordan. How are you doing?
Technician: Good. How are you?
Customer: Good, good. So, VLOOKUP and Excel, huh? Late night. Connected to my headphones real quick, if you don’t mind.
Technician: Yes, sir. I was hoping to connect on the… I’m sorry. I was just giving me an issue. Let’s see. Okay. Are we connected on the computer?
Customer: Yeah. So, if you can go to your computer and go to the email, you should get an email from Geeker that says… Let’s see. What does it say? Should say… Should be from Geeker tech support, support team. We found a tech for you. Connect now.
Technician: Yeah, the problem is I don’t have my email set up on this computer. Let me see if I can get on there quick. I’m just logging into my email. Sorry.
Customer: Yeah, no worries. All good. Is this for work or school?
Technician: For my own personal, but it’s coming from work-related functions.
Customer: Okay. Just curious. Alright. So, if I hit connect now…
Technician: Yeah, that should do it. And… Are you on a PC or a Mac?
Customer: A PC.
Technician: Okay. So, I hit connect now. It’s showing our job. I hit join.
Customer: Yeah, join. And… I should see… I just have an issue. I think the issue is going to be my… My work computer. I think my corporate IT is going to block it.
Technician: Okay. We’ll see. Let’s see. Something’s not working properly, it says.
Customer: Okay. Let’s kick you over to Zoom. I think I can see you. Let me test the chat window here. Let’s see. So, I’m going to say hi on the chat. Did you get that on your phone?
Technician: I received… I see hi in the chat bar on the computer.
Customer: Okay. So, I’m going to send you a Zoom link. That way, I think your work computer should allow you that.
Technician: Yeah. Let’s see. We’ll find out. All right. So, I’m going to copy this down there. So, grab all of that. Let’s see if we can go to Zoom. So, when you go to Zoom, don’t turn on the audio, because we’re already doing the audio on the Geekr platform. So, just keep it muted there.
Customer: All right. Let me let you… I’m going to put you as host. That way, you can share your screen there on the bottom.
Technician: All right. So, we’re in the Zoom call.
Customer: Yep. All right. So, go to the bottom.
Technician: Yeah. There we go. All right. So, let’s see here. Get my other Excel file together.
Customer: All right. You’re seeing my screen then?
Technician: Yeah. All right. All right. So, we have our two files here, right? And this is what I’m looking to do. I’ve been at this for an hour. It’s killing me. So, I have this column here. It’s called a sales order number, right? And I have a separate file, completely separate workbook, where that number is in here. So, I know it’s in here. I have my same column. It’s going to be SOA or sales order. And I’m just looking to pull over the invoice number for that sales order number.
Customer: Ah, okay. So, what I think I’m doing is, you know, the VLOOKUP. So, you can’t do VLOOKUP to the left. So, that’s what your issue is.
Technician: Ah, okay. So, you’re trying to look up in Q to go to P, but VLOOKUP won’t do that. So, no. So, insert B. That’s fine. So, let’s see what version of Excel you have. So, type equals XL. Let’s see if you have that available.
Customer: XL. Alright. So, you do. Okay. So, we’re going to use XLOOKUP. So, what that can do is it can look in column Q, and it can go backwards to the left and look at column P. So, go ahead and do that. Are you familiar with VLOOKUP?
Technician: Very, very vaguely.
Customer: Okay. Let’s go. Let’s help you out. Go to the very top, and there’s a little, you see the formula there on the top there? Right here? I’m sorry. That you’re typing.
Technician: Yeah, right where your mouse was. Sorry.
Customer: Okay. Go up higher. All the way to the top. Not scroll up. Just right a little higher with your mouse. A little higher. Right there. Click on that. So, that’s a little helper when you’re trying to make formulas. So, that’s called the F of X or the function wizard. So, we’re going to look up the cell to the left of where you currently are. So, we want to find that. Click on that one. And the lookup array, click on that next white window. We’re going to click on column Q in the other file. Yep. And we want it to return. Click on that one. The white window, sorry. And click on column P. And if there’s an error, what do you want it to say if it doesn’t find it? Just say not found or not here.
Technician: Yeah. Or nothing. So, whatever you want to type is right there. And then we’re going to do false there for the match type.
Customer: False means I want to exactly find that number. Okay. That’s it. Yeah. And then we can double click that dot, the green dot there on the bottom. Double click it, go all the way to the bottom. And then you can drag the green dot on the very bottom all the way to the top.
Technician: Okay. So, that was very helpful. Now, that’s because we’re using the XLOOKUP function. So, if I wanted to do a VLOOKUP, would I just have to reposition where I’m entering that formula?
Customer: No. What you would do is move, switch column Q and P on the original file. Just interchange them?
Technician: Yeah. So, just right click on P. So, right click P, insert. And then just grab R and move it over to P and just delete column R. And then we can change the XLOOKUP to a VLOOKUP. So, you look up column P and you bring back column. You look up the range, Papa and Quebec. So, P and Q together. And you bring back column number two. So, we can do that if you want. If you want to do a VLOOKUP here in column B, you can do that. So, it makes more sense to you, if you’d like.
Customer: There’s actually something else I want to try, if you have a moment.
Technician: Yeah. I guess I’m paying for the time, right? So, you probably have as much time as I’m going to pay for.
Customer: Yeah. I’ve never used this service before, so I’m excited to see how high this bill gets.
Technician: All right. So, here’s a separate file, separate from what we just looked at, okay?
Customer: Okay. And what it has here is it has an SOA number. Here it says SO number, but just not to confuse ourselves. That’s what I’m going to keep calling this SOA. All right. So, this is a year’s worth of data, and it’s a lot of data. This SOA number, if I’m using this file here, this one, I have the same data here. So, I want to look these up, these values here against this file, and I want to know if these SOA numbers are anywhere on this file. Okay. Would I use the same XLOOKUP function for that?
Technician: Yeah, but you see the little green arrows in the left file?
Customer: Yeah.
Technician: So, it’s not seeing that totally. So, let’s convert those numbers. So, what we can do is insert a column to the right. So, click on G and click on Insert, and we’re going to do SOA converted or whatever you want to say, and we’ll do equals value. So, we’re going to turn that text sorting into a number and then click on, yeah, to the left, click on that guy, and close bracket. So, it converts it to a number. Double-click it. And what’s going on? Why does that have green arrows now, too? That’s weird. Oh, well. So, let’s go look on V, on Bravo, on the other side. So, we’re going to do V. So, go all the way up. Yeah. To the top. Yeah. So, equals VLOOKUP, and we’re going to click on that number there on A2. Oh, sorry. Go back. My bad. Equals VLOOKUP, open bracket. There we go. Click on that, comma, and click on G, golf, and comma one, comma false, close bracket. So, if there’s an N-A, it’s not there. And we can change those N-As to something else if you don’t like the N-As.
Customer: Yeah, I think I could just do that in here. This one doesn’t have it, so what you’re going to have to do with the VLOOKUP is double-click it, and right after the equal sign, we’re going to do IFERROR, one word, open bracket, and go to the very end of the function, all the way to the back, all the way to the far end, and hit a, oh, no, don’t click anywhere. So, right before the VLOOKUP, there’s a D3 in there that got clicked on, so get rid of the D3 right before the word VLOOKUP. Yep. All right, go to the very end now and hit a comma, comma, and then in double quotes, type whatever you want it to say. One word, or can I put a space? So, go back, double quote, hit like shift quote, shift quote, there you go. Now type whatever you want, and then close the double quote there on the back end, and then close the bracket. There you go. Now double-click that green dot. There you go. So, I was doing exactly those steps before, but you’re saying because this one’s not formatted as a text, it was throwing an error. Maybe. Let’s take a look. The way I was doing it before was like this, equals VLOOKUP. I wanted to find this value against this column, put one, and I put false. I get the NA. Maybe the issue was I was going like this, but it would just tell me NA all the way down. Yeah, so that’s what it was doing. Okay. Yeah, so it was the value. Yep. Oh, okay. So, whenever I ran this file over here, it’s just it’s however this column was formatted. I need to convert that to text first, and then run the function. So, we actually converted it to a number, since it is a number. Or a number. The numbers came in and called them FOXTROT as text. So, that’s the – How did we do that again? We did equals – That was in column H. Just go to column H. Oh, hit escape, just go there. Oh, value. We did value. Yeah. Equals value, this, close parentheses, and then we double clicked, and it gave us all our values. Yeah. Now, it’s still showing that little green bar. So, what does that mean again? It all depends. Go to the bottom. I think they’ll have the yield sign there. Go to the yield sign there on the left. Click on that. What does it say? Unprotected formula. That’s – I don’t know. The one to the left probably says – are you sure? So, click on the left. That one probably says you want to convert it to a number. So, maybe I could have just done that. Yeah, you could have highlighted that entire column. No, you don’t want to do that. You don’t want to do that. All right, let me go back. So, you can highlight the entire thing. And then click on the yield sign and convert them all to numbers. And then it should be able to look in column F. It’s taking a while because there’s – Yeah, it’s a huge file. 50,000, yeah. It’s a year’s worth of data. I like the other way we did it with just the value function. It was a lot quicker. All right, well, that solves that problem. Yeah, I think I’m good. Awesome. Awesome. So, how do we wrap this thing up? This is my first time using the service because I was that curious enough to pay for it. That’s it. We’re done with the Zoom, so we just get off the Zoom call. On the Geekr portal, I’ll just close it out. And you can rate me or put comments on there or just leave it alone. Your credit card’s already on file. So, it just fills you according to how much time we use automatically. Okay, great. All right. Yeah, if you don’t mind closing out, I’d love to give you a good review. You helped me out tremendously. I really appreciate that. Yeah, my pleasure. Yeah. All right, well, thank you. Yeah, have a great night. You too. I will maybe talk to you soon. Have a great weekend. Yep. All right, you too. Bye. Bye. Bye.
