Double Lookup
By Richard Rost
4 years ago
Double Lookup in Excel with Multiple Criteria
In this Microsoft Excel tutorial, I'll teach you how to perform a double lookup with both XLookup and VLookup using multiple criteria and a helper column.
Pre-Requisite
Links
Recommended Courses
 Keywords
excel 2021, excel 2016, excel 2019, excel 365, microsoft excel, ms excel, ms excel tutorial, #msexcel, #microsoftexcel, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, Can you do a VLOOKUP with 2 lookup values, lookup in excel based on 2 criteria, Excel Lookup formulas with multiple criteria, XLookup, lookup a value based on multiple criteria, helper column
Subscribe to Double Lookup
Get notifications when this page is updated
Intro In this video, I will show you how to perform a double lookup using XLOOKUP in Microsoft Excel when you need to search based on multiple criteria such as state and city. I'll demonstrate how to set up a helper column to create unique combinations for accurate lookups, and walk through using both XLOOKUP and VLOOKUP to retrieve your data. If you are working with data where you need to match more than one field, this quick Excel tip will help you get the results you need.Transcript Welcome to another Fast Tips video brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost.
In today's video, I am going to show you how to perform a double lookup using XLOOKUP, requiring multiple criteria in Microsoft Excel. This question comes up from time to time, and I know I have covered this in various forms throughout my different courses using multiple different techniques.
You can use INDEX and MATCH, and you can use array formulas. There are all kinds of ways you can do this, but I am going to show you a quick trick to do a double lookup.
In other words, you want to look up the sales based on the values in two other columns, for example, the state and city. And yes, I actually looked up a list of cities. There are about eight states that have cities named Buffalo. That is interesting. Rochester as well.
So let's say you want to look up the sales from Buffalo, but which Buffalo? Buffalo, Illinois? Buffalo, Texas? So you have to do a double lookup.
Like I said, you can use all kinds of other techniques, but here is the easiest solution. The easiest solution is to use what is called a helper column. It is a column that puts together those other two columns and basically gives you a unique result.
Now, this assumes that there is only one combination for every item in column A. There is only one item in column B that matches with it, so if you put them all together, you get a unique combination. If you have Buffalo, New York showing up twice in this list, then this technique will not work. But assuming these are all unique, here is what we can do.
Let's insert a column right here. We will call this my helper column.
Now right here, we will use a little string concatenation to put together state and city. I like to separate them with some character that probably will never appear between them, like the pipe symbol or a caret symbol or something.
We are going to go equals A2 ampersand for string concatenation, and then inside quotes, I am going to put the little pipe symbol. That is over next to the Enter key on most keyboards, or whatever character you want. Just something that will not show up in the other two fields. Then ampersand again, and then B2.
That is what it ends up looking like. Then we will autofill this down. So there is our unique combination of column A and column B. Now this is an easy value to look up.
You can hide this column if you want to later on. You do not have to see that.
If I come over here now and I want the user to be able to type in the state, and then the city, and then get the sales, I can do that.
So we can type in, let's say, New York. And we will type in Rochester.
Then right here, I want to look up the sales for Rochester, New York. So I am basically looking up the helper column. Let us put that right here. Let's build it again. Helper is going to be equals, and it is going to be that and a little pipe symbol and that.
Now I have my value that I am actually looking up.
Now I can use my XLOOKUP or VLOOKUP if you want. If you are old school and want to use VLOOKUP, you will just have to put your helper column in the front because VLOOKUP can only look up to the right, whereas with XLOOKUP, you can look up anywhere in that table. I like XLOOKUP better.
If you do not have XLOOKUP, if you have an older version of Excel, this is a good time to upgrade. XLOOKUP is available in an Office 365 subscription and I have a video teaching you how to use it right there. It is just like VLOOKUP, very similar. I will put a link to that video down below. You can click on it to go watch that.
So it is going to be equals XLOOKUP. What is my lookup value? That guy. That is what I am looking up. Comma, where is the lookup array? In other words, where is the range of stuff you are looking up that value in? That is right there. Comma, what is the return array? In other words, where is the list of stuff you want to bring back? That is right next to it here. That is the benefit of XLOOKUP, as you can go left and right. Then enter.
There you go. There is Rochester, New York. In this case, you could use VLOOKUP because the return value is to the right of our helper column.
So we could go VLOOKUP here. We could say equals VLOOKUP. The lookup value again is our helper value. Comma, the table array is this whole thing now. A little bit different in VLOOKUP. Comma, which column of that table do you want to return? That is column two. For this one, make sure you specify exact match. When you do all that, you should get the same value.
There you go. There is your fast tip for today.
Craig, this video goes out to you. Hope this helps you.
For the rest of you, if you are not familiar with the rest of my videos, most of what I do is Microsoft Access. I love Excel, but Access is my forte.
Next up, I am going to show you how to do the same thing using a double lookup, a double DLOOKUP, in Microsoft Access.
There is that same thing in Access using DLOOKUP. There will be a link down below. You can click on that if you want to learn how to do this in Microsoft Access.
But that is your Excel fast tip for the day. I hope you learned something. I will see you next time.
So that is it. Want to learn more Excel? Be sure to like this video and subscribe to my channel. Stop by my website to watch my free Excel Level 1 course. It is over 90 minutes long and it covers all the basics.
If you want me to post more Excel videos, I need to hear from you. About 90 percent of what I do is Microsoft Access, but I am also a published Excel author and I love Excel. So if you want to see me post more free Excel videos, post a comment below and let me know. Say, hey, I want more Excel.Quiz Q1. What is the main purpose of using a double lookup in Excel as discussed in the video?
A. To search for values based on two separate criteria such as state and city
B. To filter lists based on a single column value
C. To count the number of unique values in a list
D. To create complex charts from multiple tables
Q2. What key technique does the instructor recommend for performing a double lookup?
A. Using two separate lookup functions side by side
B. Creating a helper column by concatenating the two criteria columns
C. Adding conditional formatting to each column
D. Creating a pivot table
Q3. What symbol does the instructor suggest using as a separator in the helper column?
A. A space character
B. An underscore _
C. A pipe symbol |
D. A comma ,
Q4. Why is it important for the combinations in the helper column to be unique?
A. VLOOKUP will not function otherwise
B. The formula only returns the first occurrence of a match
C. You cannot use text in Excel formulas
D. XLOOKUP does not allow duplicate values
Q5. What is one key advantage of XLOOKUP over VLOOKUP mentioned in the video?
A. XLOOKUP can return multiple results simultaneously
B. XLOOKUP can search left or right in a table, not just to the right
C. XLOOKUP can only be used in online spreadsheets
D. XLOOKUP requires fewer columns in the data set
Q6. What must be done if you want to use VLOOKUP with the helper column?
A. Place the helper column to the right of the data
B. Use VLOOKUP with approximate match only
C. Place the helper column at the front (to the left) of the data
D. Ensure each city name is unique on its own
Q7. What is required if you want to use the presented method in Excel but do not have XLOOKUP?
A. Use the double lookup with array formulas
B. Upgrade to Excel with Office 365 to access XLOOKUP
C. Use VLOOKUP but with the helper column at the front of the data
D. Both B and C
Q8. What extra step did the instructor mention you can do after creating the helper column?
A. Rename the entire data table
B. Hide the helper column if you do not want to see it
C. Delete the original columns
D. Apply conditional formatting to the helper column
Q9. If you want to use this double lookup approach in Microsoft Access, what function would you use?
A. VLOOKUP
B. XLOOKUP
C. DLOOKUP
D. HLOOKUP
Answers: 1-A; 2-B; 3-C; 4-B; 5-B; 6-C; 7-D; 8-B; 9-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Excel Learning Zone covers how to perform a double lookup using XLOOKUP in Microsoft Excel when you need to match on multiple criteria. This is a frequently asked question, and although there are multiple ways to solve it, such as using INDEX and MATCH or array formulas, today I'll explain a simple and effective approach.
There are times when you need to look up a value based on two or more columns, for example, finding the sales figure for a combination of state and city. This is especially important when you have common city names in different states, like Buffalo or Rochester, which appear in several states. Simply matching on one column won't get you the unique record you need, so a double lookup is necessary.
You might have seen different techniques for this, but the simplest way is to create what I call a helper column. The helper column combines the values of both criteria columns, such as state and city, into a single unique string. This method works as long as each combination of criteria in your data is unique. If you have duplicate pairs, this technique will not work correctly.
To create the helper column, insert a new column in your sheet. Then use string concatenation to merge the values from your two criteria columns, separating them with a character that is unlikely to occur in either field, like the pipe symbol or caret. This results in a column where every row now contains a unique value made from the two fields. You can fill this formula down for all your data rows, and, if you prefer, you can hide this helper column later.
Now, when you want to look up a result, like sales for a particular state and city, you can enter those two values in separate cells. Then, by concatenating those two entry cells with the same separator you used before, you have the actual lookup value that uniquely identifies one row in your table.
At this point, you can use XLOOKUP by specifying your concatenated value as the lookup target, the helper column as the lookup array, and the sales column as the array from which to return the result. XLOOKUP is quite flexible, allowing you to search from left to right or right to left. If you prefer to use VLOOKUP instead, make sure your helper column is positioned to the left of your data table since VLOOKUP only looks to the right. Also, in VLOOKUP, you need to specify which column to return and ensure you're using exact match mode.
If you're on an older version of Excel that does not support XLOOKUP, this may be a good time to upgrade. XLOOKUP is available with an Office 365 subscription, and I have a separate video that covers how to use it for those interested.
And that's the fast tip for today. For those of you more interested in Access, I spend most of my time working with Microsoft Access, but I also have a love for Excel. After this, I'll be showing how to accomplish a similar double lookup using DLOOKUP in Microsoft Access, and I will provide a link for those wanting to see the Access method.
If you found this helpful and want to keep learning, you can visit my website to watch my free Excel Level 1 course, which covers all the basics in over 90 minutes. If you'd like to see more Excel content in the future, let me know by leaving a comment.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Double lookup with multiple criteria using XLOOKUP
Creating a helper column with concatenated values
Choosing a unique separator for concatenation
Autofilling and hiding the helper column
Setting up input fields for state and city
Building a lookup value for double criteria
Using XLOOKUP to return values based on helper column
Using VLOOKUP with a helper column for double criteria
Differences between XLOOKUP and VLOOKUP in this context
Ensuring uniqueness of combinations in the dataset |
