XLOOKUP for google sheets is a custom function that comes handy when you want to search for things from a table or range using another row.
Listing updated:January 8, 2022
Works with:
16K+
Overview
Sample use case is if we want to find employee name based on their employee number. 
With XLOOKUP google sheet function, you can look in one column for a search term and return a result from the same row in another column, regardless of which side the return column is on.
XLOOKUP for google sheets is an improvement on three existing formulas: VLOOKUP and HLOOKUP which have same syntax however with VLOOKUP you can utilise a column number in the lookup table. The downside of the two formulas is that if the table is changed the whole formula breaks. This is something we have solved in xlookup for google sheets. The other formula is INDEX(MATCH): which is a more sophisticated solution for lookups on data on tables however more difficult for most normal users. This again has been simplified by xlookup for google sheets.

Formula one: XLOOKUPTEXT  
This is the xlookup for use in matching based on text similarity. The formula uses fuzzy lookup matching as well allowing for exact match. The function has the following parameters:
lookup_value: The value to search for If omitted, XLOOKUP returns blank cells it finds in the lookup array.
lookup_array: The column to search the lookup value in.
return_array: The column or columns to return once we find a match.
Threshold: The minimum value of text similarity btwn lookup value and lookup array

Formula two: XLOOKUPNUMBERS
This is the xlookup for use in matching numeric based primary keys. The function has the following parameters:
lookup_value: The value to search for If omitted, XLOOKUP returns blank cells it finds in the lookup array.
lookup_array: The column to search the lookup value in.
return_array: The column or columns to return once we find a match.
[if_not_found] Where a valid match is not found, return the [if_not_found] text you supply. If a valid match is not found, and [if_not_found] is missing, #N/A is returned.
[match_mode] 0 - Exact match. If none found, return #N/A. This is the default.
-1 - Exact match. If none found, return the next smaller item.1 - Exact match. If none found, return the next larger item.


Other additional features that the addon can perform are as listed below:
compare names similarity for google sheets.
vlookup with text similarity for google sheets.
vlookup text contains in google sheets.
Compare address and names similarity.
vlookup approximate match text
vlookup if cell contains a word within 

Additional information
PricingNot available
Developer
Trader status unspecified
Privacy policy
Terms of service
Xlookup for Sheets will ask for the permissions shown below. Learn more
Xlookup for Sheets will need access to your Google account
This will allow Xlookup for Sheets to :
See, edit, create, and delete all your Google Sheets spreadsheets
Display and run third-party web content in prompts and sidebars inside Google applications
Connect to an external service
See your primary Google Account email address
See your personal info, including any personal info you've made publicly available
Reviews
Language:
Sort by:
Google doesn't verify reviews or ratings. Learn more about reviews
Profile Picture
J PennHill
October 25, 2022
just what I needed -- drxns were a little unclear but hey -- I put my big-boy pants on and it was perfect. thanks to whoever wrote this.
Is this review helpful?
Yes
No
Profile Picture
NachozZ
March 18, 2022
Doesn't work even with small threshold, and didn't even mention, before installation, that you have to pay for it .
Is this review helpful?
Yes
No
Profile Picture
ABDUL KHAYYUM
August 26, 2021
this addon deserves more attention
Is this review helpful?
Yes
No
1-3 of 3
Search
Clear search
Close search
Google apps
Main menu