How to use XLOOKUP in Google Sheets, by Sheets Help on YouTube. 6 min 8 s.
- Video length
- 6 min 8 s
- Time to do it
- 10 minutes
- Difficulty
- Easy
- Steps
- 9
What you need
Steps
Open your spreadsheet and click the cell where you want the result to appear.
Type =XLOOKUP( then click the cell holding the value you want to find, such as a product name or ID, and type a comma.
Select the range to search in, like A2:A100, add a comma, then select the range that holds the answer, like C2:C100, and close the bracket.
Press Enter. The matching value appears in the cell. With no match you get an #N/A error until you add a message.
Add a fourth argument in quotes to replace the error, for example "Not found", separated by a comma before the closing bracket.
To return several columns, extend the result range to cover them all, such as C2:E100, then press Enter and the extra values fill the cells to the right.
For inexact matches, add 1 as the fifth argument to allow the next larger value, or -1 for the next smaller. Leave it blank for exact matches only.
Change the sixth argument to control search order: 1 searches top to bottom, -1 searches bottom to top, and 2 or -2 runs a faster binary search on sorted data.
Drag the fill handle down to copy the formula to other rows. Add dollar signs to lock the lookup and result ranges first.
Tips and common mistakes
XLOOKUP does not need sorted data for the default exact match, so leave the match mode at 0 for most lists.
The lookup range and the result range must be the same size, or the formula returns an error.
Lock ranges with dollar signs before copying, like $A$2:$A$100, so the formula keeps pointing at the same table.
Use the missing value argument instead of wrapping the whole formula in IFERROR.
Questions
Can XLOOKUP return a value from a column to the left?
Yes. You choose the result range separately, so it can sit to the left of the column you search in.
What is the difference between XLOOKUP and VLOOKUP?
XLOOKUP uses separate lookup and result ranges, defaults to exact matching, and can search from the bottom up. VLOOKUP counts columns from the left and defaults to an approximate match.
Does XLOOKUP work in Excel too?
Yes, in Excel 365 and Excel 2021 and later. Older versions only offer VLOOKUP and INDEX MATCH.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
