What is Basic Text Functions in XLOOKUP in Google Sheets?

What is Basic Text Functions in XLOOKUP in Google Sheets?

Google Sheets is a powerful and versatile spreadsheet application that allows you to perform various calculations, data analysis, and visualization tasks. One of the most useful features of Google Sheets is the ability to look up values in a range and return corresponding values from another range. This can be done with the XLOOKUP function, which is a newer and improved version of the traditional VLOOKUP and HLOOKUP functions.

In this article, we will explain what is the XLOOKUP function, how to use it with text values, and what are some of the benefits and limitations of this function.

What is the XLOOKUP Function?

The XLOOKUP function in Google Sheets allows you to perform complex lookups, horizontally and vertically, without having to resort to combining the INDEX and MATCH functions. You can search for a value in any row or column and return the corresponding value from the parallel row or column you specify¹.

The XLOOKUP function has six parameters, but only the first three are required. The syntax of the function is:

=XLOOKUP (search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

where:

• search_key: The value to search for. It can be text, a number, or a cell reference.
• lookup_range: The range to consider for the search. This range must be a singular row or column.
• result_range: The range to consider for the result. This range’s row or column size should be the same as the lookup_range, depending on how the lookup is done.
• missing_value: [OPTIONAL - #N/A by default] The value to return if no match is found.
• match_mode: [OPTIONAL - 0 by default] The manner in which to find a match for the search_key. 0 is for an exact match. 1 is for an exact match or the next value that is greater than the search_key. -1 is for an exact match or the next value that is lesser than the search_key. 2 is for a wildcard match.
• search_mode: [OPTIONAL - 1 by default] The manner in which to search through the lookup_range. 1 is to search from the first entry to the last. -1 is to search from the last entry to the first. 2 is to search through the range with binary search. The range needs to be sorted in ascending order first. -2 is to search through the range with binary search. The range needs to be sorted in descending order first.

How to Use XLOOKUP Function with Text Values?

To lookup a string of text, you can enter the text into the XLOOKUP function enclosed with double quotations². For example:

=XLOOKUP(“Sub 2”,B3:B7,C3:C7)

This will return the status of Sub 2 from column C, based on its position in column B.

Or, you can reference a cell that contains text². For example:

=XLOOKUP(E3,B3:B7,C3:C7)

This will return the status of whatever text value is in cell E3 from column C, based on its position in column B.

If you want to look up a series of texts, enter the range reference into the formula, and a dynamic array “Spill” formula will be created². For example:

=XLOOKUP(E3:E4,B3:B7,C3:C7)

The formula will “spill” to perform the calculations on the entire array of values.

Alternatively, you can reference a single cell, lock cell references, and copy the formula down². For example:

=XLOOKUP(E3,$B$3:$B$7,$C$3:$C$7)

Note: Make sure that the 2nd and 3rd arguments are in absolute references by adding dollar signs in front of the Column Letter and Row Number (or you can put the cursor in the reference while in the formula and press F4). This will fix the references while you’re dragging or copying the formula.

How to Use XLOOKUP Function with Text Functions?

You can also use text functions within the XLOOKUP function to manipulate or extract text values before performing the lookup. For example:

=XLOOKUP(LEFT(E3,4),B3:B7,C3:C7)

This will use the LEFT function to get the first four characters of cell E3 and then look up that value in column B and return its status from column C.

Some of the common text functions that you can use with XLOOKUP are:

• LEFT: Returns a specified number of characters from the left side of a text string.
• RIGHT: Returns a specified number of characters from the right side of a text string.
• MID: Returns a specified number of characters from a text string starting at a specified position.
• LEN: Returns the number of characters in a text string.
• TRIM: Removes extra spaces from a text string.
• UPPER: Converts a text string to uppercase.
• LOWER: Converts a text string to lowercase.
• PROPER: Capitalizes the first letter of each word in a text string.
• CONCATENATE: Joins two or more text strings into one text string.
• TEXT: Converts a value to text in a specified format.

What are the Benefits and Limitations of XLOOKUP Function?

The XLOOKUP function has several advantages over the traditional VLOOKUP and HLOOKUP functions, such as:

• It can perform both horizontal and vertical lookups, without having to transpose the data or use the INDEX and MATCH functions.
• It can look up values from the left or right, and from the top or bottom, by changing the search_mode argument.
• It can handle arrays and spill formulas, making it more dynamic and flexible.
• It can return multiple values or entire rows or columns, by specifying a larger result_range argument.
• It can handle errors and missing values, by specifying a missing_value argument.
• It can perform exact, approximate, or wildcard matches, by changing the match_mode argument.

However, the XLOOKUP function also has some limitations, such as:

• It is not compatible with older versions of Excel or Google Sheets. You need to have Excel 365 or Google Sheets version 2021 or later to use it.
• It is not supported in BigQuery, which is a cloud-based data warehouse service from Google. You need to use the XLOOKUP for BigQuery function instead³, which has a slightly different syntax and parameters.
• It is case-insensitive by default, which means that it will match text values regardless of their case (upper or lower). You need to use the EXACT function to perform a case-sensitive match².
• It does not support partial matches by default, which means that it will only match whole texts or phrases. You need to use the wildcard characters (*) and (?) to perform a partial match¹.

Conclusion

The XLOOKUP function in Google Sheets is a powerful and versatile function that allows you to look up values in a range and return corresponding values from another range. You can use it with text values, as well as numbers, dates, and other data types. You can also use it with text functions to manipulate or extract text values before performing the lookup. The XLOOKUP function has several benefits over the traditional VLOOKUP and HLOOKUP functions, but it also has some limitations that you need to be aware of.

We hope this article has helped you understand what is basic text functions in XLOOKUP in Google Sheets and how to use them effectively. If you have any questions or feedback, please feel free to leave a comment below.

Tweet

Read More..