VLOOKUP vs XLOOKUP in Sourcetable

VLOOKUP has been the gold standard for lookup formulas since its creation in 1985, but in 2019 XLOOKUP debuted, creating a new rival for the top spot. Let's find out which formula is superior. VLOOKUP and XLOOKUP are great ways to pull data from a table in Sourcetable. They are both available out of the box in the Sourcetable application. You are probably asking yourself, "Aside from the age of the formulas what are the key differences? In this article we will go over each key difference below.

Wildcards

Wildcards are special characters you can use in a spreadsheet function instead of a particular letter or number. They stand-in for a specific letter or number and allow for more flexibility when looking up data.

The three main wildcards are:

1. * Asterisk

   • * stands in for any number of characters
   • Such as: *west would find Southwest and Northwest

2. ? Question Mark

   • ? stands in for any single character
   • Such as: Trac? would find Traci and Tracy

3. ~ Tilde

   • ~ finds a question mark, asterisk, or tilde
   • Such as: ankd~? finds ankd?

There is a huge difference between the two formulas when it comes to the use of wildcards. XLOOKUP can use wildcards to find values. VLOOKUP cannot use wildcards.

Lookup Direction

Whenever you use a formula to find values in a data set the app will lookup the value from a certain location in the data set then move in a direction within the data set to find the value. VLOOKUP can only go in one direction, left. It starts looking up the value with the unique identifier in the leftmost position in the dataset then looks from left to right. XLOOKUP is more flexible and can look from left-to-right or right-to-left.

Fuzzy Search

VLOOKUP is limited to only looking up the first match it finds whereas XLOOKUP can find all matches. The default for XLOOKUP is an exact match but you can change it or use wildcards for a "fuzzy search" to get an approximate value. If there are no matches found,then an #N/A is returned by default, but you can choose to have the next closest item returned rounded up or down. With XLOOKUP you can also choose the value that is returned when there is an error.

Limitations

Every spreadsheet formula has limitations. VLOOKUP and XLOOKUP are no different as you can see below:

XLOOKUP:

• Availability
  • If you use an older version of excel XLOOKUP may not be available
• Returning an incorrect value without proper data sorting
  • Only when using binary search

VLOOKUP:

• No custom message when the value is not found
• No lookup by row
• Will not return more than one value
• Does not search from the bottom up
• Will not round up for approximate search
• Does not have exact match as the default
  • Results in returning incorrect values

Conclusion

Whether you need to find one piece of data or many VLOOKUP and XLOOKUP are there to help.

When it comes to using VLOOKUP or XLOOKUP there is a clear winner. XLOOKUP!

It is more flexible and has more options for directionality and use. When in doubt use XLOOKUP.