VLOOKUP vs. XLOOKUP In Google Sheets
- 6 days ago
- 2 min read
In this post, we will examine VLOOKUP and the differences to its modern successor, XLOOKUP, in Google Sheets.
So what do these functions do? Have you ever had two separate lists of data and wished you could automatically pull information from one into the other? Maybe you have a list of products or services, and you need to find their prices from a master sheet. Instead of manually searching through our extensive list of training sessions, we can use VLOOKUP or XLOOKUP to match them with their corresponding prices.
The XLOOKUP function is an improved alternative to VLOOKUP. Simply put, it is more flexible, less prone to breaking and easier to write - it does a lot of the work for you.
In detail, the benefits of switching to XLOOKUP are:
Search Direction: VLOOKUP can only search from left to right, meaning the search key must be to the left of the data being searched, whereas XLOOKUP can search in any direction.
Formula Integrity: If columns are inserted after a VLOOKUP formula is written, it can break the formula, but the XLOOKUP stays intact.
Error Messages: With VLOOKUP, adding error messages requires using an additional function called IFERROR. XLOOKUP had the capability built into the formula, allowing an error message to be added while writing the XLOOKUP for values not found.
Default Match: To ensure an exact match with VLOOKUP, you often need to add FALSE or a zero at the end of the formula. XLOOKUP performs an exact match by default!
Faster: on larger and more complex datasets.
In the example below, we want to know the cost of our 'Drive File Sharing' tips session
We begin the formula with =XLOOKUP
Then the search key, which in this case is ("Drive File Sharing",
Then the look up range, we want it to look at column B, so B:B
Then the result range, which is column D for us, so D:D
=XLOOKUP("Drive File Sharing", B:B, D:D)
This is simpler than =VLOOKUP("Drive file sharing tips", B2:D36, 3, FALSE)
If you currently use VLOOKUP, have a go with XLOOKUP and let us know what you think!
If you would like to learn more about Google Sheets, book one of CloudShed's live sessions or purchase our on-demand courses.
