Latasha asks how to VLOOKUP to three worksheets. Her product number might be found on the master product list, but it might be on the old product list, or it might be on the rare product list. She needs a formula that will get the country code from one of those three sheets. Today's episode shows one way to solve this problem. There might be others - post your alternative in the YouTube Comments.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL. Learn excel from MrExcel podcast. Episode # 1861 VLOOKUP to All Three Sheets?!
Hey welcome back to MrExcel netcast. I'm Bill Jelen.
Today a great question sent in by Latasha.
Latasha has a bunch of product IDs here, and she needs to get a country code, and it's a simple VLOOKUP.
Here's the main product list but here is the tricky part.
Most of the products 80% of them are here on the Main Product List but some of them are on this other sheet the Backup Product List and a few of them are on this Rarely Used Product List.
Now why doesn't Latasha comment . That's a long story.
The situation is we have to look on all three.
So how do we do a VLOOKUP that looks at all three.
Now I am going to challenge you to in the YouTube comments to give me a better solution for this.
But my first solution is hey, let's just do a VLOOKUP.
That goes to the first sheet alright, so look up for me to here in the Main Product List F 4 that , 2 ,False and double-click to shoot that down and what we should see is 80% of the time we get the answer and 20% of time, we get something that is not the answer. So I'm going to wrap this in IFERROR now Latasha has Excel 2010 so we have to use IFERROR.
If she had Excel 2013 we could use IFNA But you know Excel 2010 IFERROR is great so Ctrl C. What we're going to do is say.
hey, go, do that VLOOKUP to the first sheet and if that works, great. Just stop don't do anything else, but if it fails then Go do a VLOOKUP to the second sheet.
Alright so backspace through this.
It was kind of silly to copy and paste while what I really needed to do was the VLOOKUP A2.
Here's the Backup Product List F4 , 2 , False and close the other parentheses. All right so now, it's working almost all the time, but there's those few products, the ones that are coming off the last product list that I'm still getting an NA. So one more IFERROR, but I'm not going to put it here at the beginning.
I'm going to put it there right before the VLOOKUP, the second VLOOKUP.
So if that one is an error then we know that we have to go do another VLOOKUP using A2 this time going back to that Rarely Used List of products F4 , 2 , False All right, and that closes the VLOOKUP that closes the IFERROR that closes the original IFERROR and there we go.
We now have our answers coming from one of the three sheets.
It's important that whichever sheet has the most products kind of is the first VLOOKUP and then when that fails 20% of time when that fails, it goes out and does the second VLOOKUP.
When that fails the last few times, it's going to go out and do the third VLOOKUP.
That's my solution. Now I won't even think about this for 10 minutes, and there's probably something a lot better.
So go ahead and let us know in the YouTube comments if you have a better way of solving that.
All right well hey I want to thank Latasha for sending that question in.
And I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
Hey welcome back to MrExcel netcast. I'm Bill Jelen.
Today a great question sent in by Latasha.
Latasha has a bunch of product IDs here, and she needs to get a country code, and it's a simple VLOOKUP.
Here's the main product list but here is the tricky part.
Most of the products 80% of them are here on the Main Product List but some of them are on this other sheet the Backup Product List and a few of them are on this Rarely Used Product List.
Now why doesn't Latasha comment . That's a long story.
The situation is we have to look on all three.
So how do we do a VLOOKUP that looks at all three.
Now I am going to challenge you to in the YouTube comments to give me a better solution for this.
But my first solution is hey, let's just do a VLOOKUP.
That goes to the first sheet alright, so look up for me to here in the Main Product List F 4 that , 2 ,False and double-click to shoot that down and what we should see is 80% of the time we get the answer and 20% of time, we get something that is not the answer. So I'm going to wrap this in IFERROR now Latasha has Excel 2010 so we have to use IFERROR.
If she had Excel 2013 we could use IFNA But you know Excel 2010 IFERROR is great so Ctrl C. What we're going to do is say.
hey, go, do that VLOOKUP to the first sheet and if that works, great. Just stop don't do anything else, but if it fails then Go do a VLOOKUP to the second sheet.
Alright so backspace through this.
It was kind of silly to copy and paste while what I really needed to do was the VLOOKUP A2.
Here's the Backup Product List F4 , 2 , False and close the other parentheses. All right so now, it's working almost all the time, but there's those few products, the ones that are coming off the last product list that I'm still getting an NA. So one more IFERROR, but I'm not going to put it here at the beginning.
I'm going to put it there right before the VLOOKUP, the second VLOOKUP.
So if that one is an error then we know that we have to go do another VLOOKUP using A2 this time going back to that Rarely Used List of products F4 , 2 , False All right, and that closes the VLOOKUP that closes the IFERROR that closes the original IFERROR and there we go.
We now have our answers coming from one of the three sheets.
It's important that whichever sheet has the most products kind of is the first VLOOKUP and then when that fails 20% of time when that fails, it goes out and does the second VLOOKUP.
When that fails the last few times, it's going to go out and do the third VLOOKUP.
That's my solution. Now I won't even think about this for 10 minutes, and there's probably something a lot better.
So go ahead and let us know in the YouTube comments if you have a better way of solving that.
All right well hey I want to thank Latasha for sending that question in.
And I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
