XLOOKUP
Tests the search_table for the given lookup_value and returns a corresponding value from the result_table. XLOOKUP is an advanced version of the VLOOKUP and HLOOKUP functions that searches both vertically and horizontally.
Syntax
lookup_value: The value to search for in the search_table. Eg., F3
search_table: Range with values to evaluate. Eg., C2:C13
result_table: Range that you want the corresponding value from as result. Eg., A2:B13
if_not_found: Text to be displayed when no valid match is found. Defaults to #N/A error if omitted. Eg., "Invalid Emp ID"
match_mode: Determines the type of match to perform. Defaults to 0, if omitted.
Supported match_mode:
0 - The first exact match is considered.
-1 - The first exact match is considered. If none found, it returns the next smallest value.
1 - The first exact match is considered. If none found, it returns the next largest value.
2 - A regex match where .*, .?, and / have special meaning.
search_mode: Determines how lookup_value is searched in search_table. . Defaults to 1, if omitted.
Supported search_value:
1 - Search from top to bottom in the given range.
-1 - Search from bottom to top in the given range.
2 - Perform a binary search with the search_table sorted in ascending order. If not sorted, invalid results will be returned.
Remarks
-
XLOOKUP can be used to bring corresponding data from other worksheets.
-
Use named ranges for search_table and result_table . This helps refer a single source data table to create multiple tables for calculation and analysis.
-
If you don't remember the complete text you have to search for, use .*, .? and / instead.
.* - Can be used to replace any number of characters. Eg.,
Zyl.*
finds
Zylker
.? - Can be used to replace a single character. Eg.,
Mar.?us
finds
Marcus
and
Markus
/ - Can be used to include *, ? and / in the search term. Eg.,
Zylker/*
finds
Zylker*
and
Mar.?us/?
finds
Marcus?
and
Markus?
Examples
|
Formula
|
Result
|
|
=XLOOKUP(F3;C2:C13;A2:B13;"Invalid Emp Id")
|
Sarah Jones | 9/14/95
|
|
=XLOOKUP("Ben.*";A2:A13;C2:C13;"No result found";2)
|
4323
|
|
=XLOOKUP(I2;M$2:M$10;N$2:N$10)
|
Groceries
|
Possible Errors
|
Errors
|
Meaning
|
|
#N/A!
|
|
|
#NAME!
|
|
|
#VALUE!
|
|
|
#REF!
|
|
Similar Functions
Access your files securely from anywhere
Zoho CRM Training Programs
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
Zoho DataPrep Personalized Demo
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
- Zoho Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
Create. Review. Publish.
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.
Zoho Sheet Resources
Zoho Forms Resources
Zoho Sign Resources
Sign, Paperless!
Zoho TeamInbox Resources
Design. Discuss. Deliver.
Related Articles
Wildcard characters in formulas
Wildcard characters help you search for terms with iterations of single or multiple characters. Wildcards can be used in formulas such as XLOOKUP to include relevant terms in match. Below is the list of wildcard characters and their use cases: ...LOOKUP
Searches for a given value in a sorted row/column and returns a corresponding value from the given row/column. If more than one exact match appears, LOOKUP takes the value in a random order. If no exact match appears, LOOKUP takes the next smallest ...XMATCH
Returns the relative position of the given value in a given range. XMATCH is an advanced version of the MATCH function. Syntax XMATCH(search_item; search_region; [match_mode]; [search_mode]) search_item: The value to search for in the ...VLOOKUP
VLOOKUP checks the first column of the given data table for the specified value and returns a corresponding value, in that row, based on the column given. Syntax VLOOKUP( lookup_value; data_table; column_index; [mode] ) lookup_value : The value ...