XLOOKUP vs VLOOKUP: When to Use Each One

XLOOKUP and VLOOKUP both look up a value in a table and return a related value, but they work differently. This post puts the two side by side: what XLOOKUP can do that VLOOKUP cannot do directly, and three VLOOKUP tricks worth knowing. Each function has its own video below.

What XLOOKUP does

XLOOKUP searches a range or array and returns a matching value. It replaced several older lookup functions, which makes it more versatile than VLOOKUP. Watch the XLOOKUP tutorial first:

Key benefits of XLOOKUP

  • It looks up data to the right or to the left of the lookup value
  • It can return multiple results
  • It defaults to an exact match
  • It works with vertical and horizontal data
  • It can perform a reverse search
  • It can return entire rows or columns, not just one value
  • It works with arrays natively, so complex formulas are easier to apply

XLOOKUP example 1: a basic lookup

You have a company name and want its headcount. XLOOKUP takes the lookup value, the lookup array (the column to search) and the return array (the column to return the value from). If you know VLOOKUP, this is a direct replacement for it.

XLOOKUP example 2: look up from right to left

You have the company type and want the company name, which means going from right to left. VLOOKUP cannot do this directly, and the usual workaround was INDEX and MATCH, which is more complex. With XLOOKUP you give the lookup value, the lookup array (the type column) and the return array (the company column).

XLOOKUP example 3: horizontal data and match modes

When the data runs across rows, you would normally use HLOOKUP. XLOOKUP handles it too. In the video, the lookup value is 33 units, the lookup array is the row of unit numbers, and the return array is the row of discounts. 33 does not exist in the data, so the match mode decides what comes back:

  • Exact match or next smaller item (-1): 33 units returns the 5% discount.
  • Exact match or next larger item (+1): 33 units returns the 7% discount.

XLOOKUP also has an optional argument for what to show if the value is not found, which sits before the match mode.

Three VLOOKUP tricks

VLOOKUP is still widely used, and these tricks stretch what it can do. Watch the VLOOKUP tutorial:

Trick 1: look up a value that starts with your text

Join the lookup value to a wildcard inside quotation marks. VLOOKUP then finds the value that starts with the text you give it. In the video, column 5 is returned for the pricing.

Trick 2: sum several columns with SUM and VLOOKUP

Put SUM around VLOOKUP and list the column numbers in curly brackets: columns 3, 4, 5 and 6 in the video. Confirm the formula with Shift+Ctrl+Enter to get the total of those columns in one formula.

Trick 3: ignore extra spaces with TRIM

If the lookup value or the table contains an extra space, VLOOKUP returns an error. Wrap the lookup value in TRIM to fix the first case. If the table itself has extra spaces, use TRIM on the table array as well and confirm with Shift+Ctrl+Enter.

Bonus trick: VLOOKUP in reverse

To return a value from a column to the left of your lookup column, use the CHOOSE function inside the table array. It rearranges the columns so the lookup column comes first, and you confirm the formula with Shift+Ctrl+Enter. This is how VLOOKUP can work from right to left.

XLOOKUP vs VLOOKUP: which should you use?

  • Use XLOOKUP when you need to look left, search horizontal data, return more than one value, or want exact match as the default. It does these directly, with no workaround.
  • Use the VLOOKUP tricks when you are working with VLOOKUP and want a starts-with search, a total across several columns, or a fix for extra spaces. The CHOOSE trick also lets VLOOKUP return values from the left.

Both videos use dummy data for learning. Try them on your own data, or generate practice data with our Excel Sample Data Generator.

Frequently asked questions

What does XLOOKUP replace?

XLOOKUP has replaced several older lookup functions. In the video, it is shown as a direct replacement for VLOOKUP in a basic lookup, for INDEX and MATCH in a right-to-left lookup, and for HLOOKUP with horizontal data.

Can VLOOKUP look to the left?

Not directly. XLOOKUP can look left or right. In VLOOKUP, the CHOOSE trick above lets you return values from the left.

What is the default match in XLOOKUP?

Exact match. You can change it to the next smaller or next larger item with the match mode argument.

Similar Posts