EPISODE · Feb 16, 2020 · 17 MIN
Using XLOOKUP in Microsoft Excel - Episode 216
from Piltch Point (Video) · host PLUGHITZ Live
This week, Avram Piltch discusses a new feature in Microsoft Excel: XLOOKUP. Now, we don't usually show off things like Microsoft Office functions, but this one is truly special. The predecessor to XLOOKUP, which is VLOOKUP, is the third most used function in Excel, and XLOOKUP expands on its capabilities by removing a number of limitations.Both functions allow you to combine data from different sheets or files by joining on common data. For example, if you have one document with employee IDs and names, and another with employee IDs and addresses and phone numbers, these functions will allow you to join on the common data item, the employee IDs, and show the combined data. However, there are some significant differences between the old and new methods.The most important change is in how you match data. With VLOOKUP, you could only do exact matches between sheets. With XLOOKUP, however, there are several ways of joining data. You can do the exact match, but you can also have it choose values above and below the closest match, or, most importantly, use wildcards. For example, if you want to return the first employee whose last name starts with S, you can search for "S*" rather than using an entire cell value.Equally important is the amount of data that can be returned. With the older VLOOKUP, you could only return a single column worth of data per query. This means that if you wanted to return first name, last name, and address from one sheet, you would have to do three separate lookups. That means processing power and additional scripting to return everything you want. With the new XLOOKUP, however, you can return multiple columns in a single query. So, one lookup could return the first, last, and address.In addition to all of this, the function call is actually shorter for XLOOKUP, making it easier to use. If you're about to learn about joining data in Excel, XLOOKUP is definitely the way to go.
What this episode covers
DescriptionThis week, Avram Piltch discusses a new feature in Microsoft Excel: XLOOKUP. Now, we don't usually show off things like Microsoft Office functions, but this one is truly special. The predecessor to XLOOKUP, which is VLOOKUP, is the third most used function in Excel, and XLOOKUP expands on its capabilities by removing a number of limitations.Both functions allow you to combine data from different sheets or files by joining on common data. For example, if you have one document with employee IDs and names, and another with employee IDs and addresses and phone numbers, these functions will allow you to join on the common data item, the employee IDs, and show the combined data. However, there are some significant differences between the old and new methods.The most important change is in how you match data. With VLOOKUP, you could only do exact matches between sheets. With XLOOKUP, however, there are several ways of joining data. You can do the exact match, but you can also have it choose values above and below the closest match, or, most importantly, use wildcards. For example, if you want to return the first employee whose last name starts with S, you can search for "S*" rather than using an entire cell value.Equally important is the amount of data that can be returned. With the older VLOOKUP, you could only return a single column worth of data per query. This means that if you wanted to return first name, last name, and address from one sheet, you would have to do three separate lookups. That means processing power and additional scripting to return everything you want. With the new XLOOKUP, however, you can return multiple columns in a single query. So, one lookup could return the first, last, and address.In addition to all of this, the function call is actually shorter for XLOOKUP, making it easier to use. If you're about to learn about joining data in Excel, XLOOKUP is definitely the way to go.ParticipantsScott ErtzHostScott is a developer who has worked on projects of varying sizes, including all of the PLUGHITZ Corporation properties. He is also known in the gaming world for his time supporting the rhythm game community, through DDRLover and hosting tournaments throughout the Tampa Bay Area. Currently, when he is not working on software projects or hosting F5 Live: Refreshing Technology, Scott can often be found returning to his high school days working with the Foundation for Inspiration and Recognition of Science and Technology (FIRST), mentoring teams and helping with ROBOTICON Tampa Bay. He has also helped found a student software learning group, the ASCII Warriors, currently housed at AMRoC Fab Lab.Avram PiltchHostAvram's been in love with PCs since he played original Castle Wolfenstein on an Apple II+. Before joining Tom's Hardware, for 10 years, he served as Online Editorial Director for sister sites Tom's Guide and Laptop Mag, where he programmed the CMS and many of the benchmarks. When he's not editing, writing or stumbling around trade show halls, you'll find him building Arduino robots with his son and watching every single superhero show on the CW.Live DiscussionPowered by PureVPN
NOW PLAYING
Using XLOOKUP in Microsoft Excel - Episode 216
No transcript for this episode yet
Similar Episodes
Jun 20, 2026 ·2m
Jun 20, 2026 ·2m
Jun 15, 2026 ·3m
Jun 15, 2026 ·3m
Jun 14, 2026 ·2m
Jun 14, 2026 ·2m