Copy the name of the sheet from your colleague\u2019s spreadsheet.<\/li>\n<\/ol>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/how_to_use_xlookup_in_google_sheets_9.webp” alt=”XLOOKUP between two spreadsheets in Google Sheets \u2014 Test Scores tab name at the bottom of the file” title_text=”Test Scores tab name at the bottom of the file” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
4. Replace the two \u201cSheet1\u201d in the formula with your colleague\u2019s sheet name, \u201cTest Scores.\u201d This is what your final formula will look like:<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=XLOOKUP(A2, IMPORTRANGE(“https:\/\/docs.google.com\/spreadsheets\/d\/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Test Scores!A:A”), IMPORTRANGE(“https:\/\/docs.google.com\/spreadsheets\/d\/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Test Scores!B:B”), “Not Found”)<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
5. In the cell under “Test Score” for Jane Smith, use the XLOOKUP formula above.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/how_to_use_xlookup_in_google_sheets_10.webp” alt=”XLOOKUP between two spreadsheets in Google Sheets \u2014 XLOOKUP with IMPORTRANGE typed in cell C2″ title_text=”XLOOKUP with IMPORTRANGE typed in cell C2″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
6. Press \u201cEnter\u201d on your keyboard or click anywhere on your sheet. The score will appear.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/how_to_use_xlookup_in_google_sheets_11.webp” alt=”XLOOKUP between two spreadsheets in Google Sheets \u2014 score 85 returned for John Doe from the other file” title_text=”Score 85 returned for John Doe from the other file” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
7. If you want to get Jane Smith\u2019s score in row 3, change \u201c=XLOOKUP(A2\u201d to \u201c=XLOOKUP(A3.\u201d<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/how_to_use_xlookup_in_google_sheets_12.webp” alt=”XLOOKUP between two spreadsheets in Google Sheets \u2014 Jane Smith’s ID 102 and score 78 highlighted” title_text=”Jane Smith’s ID 102 and score 78 highlighted” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Old formula with (A2:<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=XLOOKUP(A2, IMPORTRANGE(“https:\/\/docs.google.com\/spreadsheets\/d\/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Test Scores!A:A”), IMPORTRANGE(“https:\/\/docs.google.com\/spreadsheets\/d\/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Test Scores!B:B”), “Not Found”)<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
New formula with (A3:<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=XLOOKUP(A3, IMPORTRANGE(“https:\/\/docs.google.com\/spreadsheets\/d\/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Test Scores!A:A”), IMPORTRANGE(“https:\/\/docs.google.com\/spreadsheets\/d\/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Test Scores!B:B”), “Not Found”)<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/how_to_use_xlookup_in_google_sheets_13.webp” alt=”XLOOKUP between two spreadsheets in Google Sheets \u2014 formula changed to A3 for Jane Smith in row 3″ title_text=”Formula changed to A3 for Jane Smith in row 3″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”xlookup-examples-in-google-sheets” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
XLOOKUP examples in Google Sheets<\/h2>\n
Now that you know how to use XLOOKUP to find an exact match for a single value, it\u2019s time to learn about different ways of using it. In the examples below, you will learn to use XLOOKUP to perform horizontal lookups, search using multiple criteria, return several columns at once, search using wildcards, and use named ranges. The last examples show a default value for missing matches, FILTER for returning every match, and how to find the last or the closest match.<\/p>\n
Horizontal XLOOKUP (instead of HLOOKUP)<\/h3>\n
XLOOKUP also searches across a row, so you can use it instead of HLOOKUP. Follow the steps below to perform a horizontal lookup.<\/p>\n
1.<\/strong> In an empty cell, type the XLOOKUP formula and add the search key as the first parameter. In this case, the search key will be the column header you want to match.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-horizontal-search-key.webp” alt=”Horizontal XLOOKUP in Google Sheets \u2014 column header email used as the search key” title_text=”Column header email used as the search key” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
2.<\/strong> For the second parameter, select the row where you want to find the search key. In this case, the headers are in row 1.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-horizontal-lookup-range.webp” alt=”Horizontal XLOOKUP in Google Sheets \u2014 header row 1:1 selected as the lookup range” title_text=”Header row 1:1 selected as the lookup range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
3.<\/strong> For the third parameter, specify the row from which you want the result. Skip the optional parameters to get the defaults.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-horizontal-result-range.webp” alt=”Horizontal XLOOKUP in Google Sheets \u2014 row 15 selected as the result range” title_text=”Row 15 selected as the result range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
4.<\/strong> Close the parenthesis and press \u2018Enter\u2019 to get the result.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-horizontal-result.webp” alt=”Horizontal XLOOKUP in Google Sheets \u2014 email from row 15 returned by the formula” title_text=”Email from row 15 returned by the formula” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
XLOOKUP with multiple criteria<\/h3>\n
To search for multiple values using XLOOKUP, you can combine it with ARRAYFORMULA.<\/p>\n
1.<\/strong> In an empty cell, type the XLOOKUP function.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-multiple-criteria-function.webp” alt=”XLOOKUP with multiple criteria in Google Sheets \u2014 formula typed next to the first and last name keys” title_text=”Formula typed next to the first and last name keys” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
2.<\/strong> For the first parameter, select the first search key, add an ampersand (&), and select the second search key.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-multiple-criteria-keys.webp” alt=”XLOOKUP with multiple criteria in Google Sheets \u2014 search keys I14 and I15 joined with an ampersand” title_text=”Search keys I14 and I15 joined with an ampersand” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
3.<\/strong> For the second parameter, type the ARRAYFORMULA function and select the columns where you want to find the search keys, joined by an ampersand (&).<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-multiple-criteria-arrayformula.webp” alt=”XLOOKUP with multiple criteria in Google Sheets \u2014 ARRAYFORMULA joining the first and last name columns” title_text=”ARRAYFORMULA joining the first and last name columns” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
4.<\/strong> For the third parameter, select the column from which you want the result returned.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-multiple-criteria-result-range.webp” alt=”XLOOKUP with multiple criteria in Google Sheets \u2014 amount column F:F selected as the result range” title_text=”Amount column F:F selected as the result range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
5.<\/strong> Close the parenthesis and press \u2018Enter\u2019 to get the result.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-multiple-criteria-result.webp” alt=”XLOOKUP with multiple criteria in Google Sheets \u2014 amount $1,590.52 returned for Inessa Hoyes” title_text=”Amount $1,590.52 returned for Inessa Hoyes” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Return several columns with one XLOOKUP<\/h3>\n
To return several values from the matching row, such as the email, product, and amount, you can use the ARRAYFORMULA function. XLOOKUP still returns only the first match. To get every matching row, use FILTER, as shown further down.<\/p>\n
1.<\/strong> In an empty cell, type the XLOOKUP function and add the search key as the first parameter.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-several-columns-search-key.webp” alt=”XLOOKUP in Google Sheets \u2014 last name Possa used as the search key to return several columns” title_text=”Last name Possa used as the search key to return several columns” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
2.<\/strong> For the second parameter, select the column where you want to find the search key.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-several-columns-lookup-range.webp” alt=”XLOOKUP in Google Sheets \u2014 last_name column picked as the lookup range for Possa” title_text=”Last_name column picked as the lookup range for Possa” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
3.<\/strong> For the third parameter, add the ARRAYFORMULA function and select the columns from which you want the results.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-several-columns-arrayformula.webp” alt=”XLOOKUP in Google Sheets \u2014 ARRAYFORMULA(D:F) used as the result range” title_text=”ARRAYFORMULA(D:F) used as the result range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
4.<\/strong> Close both parentheses and press \u2018Enter\u2019 to see the results.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-several-columns-result.webp” alt=”XLOOKUP in Google Sheets \u2014 email, product and amount returned in one row for Possa” title_text=”Email, product and amount returned in one row for Possa” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
XLOOKUP with wildcards<\/h3>\n
If you only have or remember bits and pieces of your search key, you can use wildcard characters to find a match.<\/p>\n
\n- *<\/strong> matches zero or more characters.<\/li>\n
- ?<\/strong> matches exactly one character.<\/li>\n
- ~<\/strong> nullifies wildcard characters so you can search for them.<\/li>\n<\/ul>\n
1.<\/strong> Type the partial search key into a cell for later reference.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-wildcard-partial-key.webp” alt=”XLOOKUP with wildcards in Google Sheets \u2014 partial search key bridge typed in a cell” title_text=”Partial search key bridge typed in a cell” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
2.<\/strong> In an empty cell, type the XLOOKUP function.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-wildcard-function.webp” alt=”XLOOKUP with wildcards in Google Sheets \u2014 formula typed next to the partial search key” title_text=”Formula typed next to the partial search key” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
3.<\/strong> Add the wildcard and partial search key, as shown below. The wildcard character should be between quotation marks and joined to the partial search key using an ampersand (&).<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-wildcard-asterisk.webp” alt=”XLOOKUP with wildcards in Google Sheets \u2014 asterisk joined to the partial search key with an ampersand” title_text=”Asterisk joined to the partial search key with an ampersand” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
4.<\/strong> For the second parameter, select the column where you want to find the match.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-wildcard-lookup-range.webp” alt=”XLOOKUP with wildcards in Google Sheets \u2014 last_name column C:C selected as the lookup range” title_text=”Last_name column C:C selected as the lookup range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
5.<\/strong> For the third parameter, select the column from which you want to get the result.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-wildcard-result-range.webp” alt=”XLOOKUP with wildcards in Google Sheets \u2014 email column D:D selected as the result range” title_text=”Email column D:D selected as the result range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
6.<\/strong> Since you\u2019re searching using a wildcard, you\u2019ll need to specify that the fifth parameter is \u201c2\u201d. However, you don\u2019t need to specify the fourth and sixth parameters if you want the default values.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-wildcard-result.webp” alt=”XLOOKUP with wildcards in Google Sheets \u2014 match_mode 2 added and the matching email returned” title_text=”Match_mode 2 added and the matching email returned” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
XLOOKUP with named ranges<\/h3>\n
Finally, let\u2019s see how you can use named ranges in XLOOKUP to make the process even easier.<\/p>\n
1.<\/strong> Open the spreadsheet with your table and go to Data > Named ranges.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-named-ranges-menu.webp” alt=”XLOOKUP with named ranges in Google Sheets \u2014 Named ranges in the Data menu” title_text=”Named ranges in the Data menu” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
2.<\/strong> From the sidebar, click \u201cAdd a range\u201d.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-add-a-range.webp” alt=”XLOOKUP with named ranges in Google Sheets \u2014 Add a range in the Named ranges sidebar” title_text=”Add a range in the Named ranges sidebar” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
3.<\/strong> Select the column you want to name and type in the name. Click \u201cDone\u201d.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-name-range.webp” alt=”XLOOKUP with named ranges in Google Sheets \u2014 range B2:B1001 named first_name” title_text=”Range B2:B1001 named first_name” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
4.<\/strong> Repeat steps 2 and 3 until you\u2019ve named all the necessary columns.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-named-ranges-list.webp” alt=”XLOOKUP with named ranges in Google Sheets \u2014 named ranges listed for every column of the table” title_text=”Named ranges listed for every column of the table” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
5.<\/strong> In an empty cell, type the XLOOKUP function and add the search key.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-named-search-key.webp” alt=”XLOOKUP with named ranges in Google Sheets \u2014 formula typed with the search key Rennick” title_text=”Formula typed with the search key Rennick” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
6.<\/strong> For the second parameter, start typing the name of the column to search and select it from the dropdown.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-named-lookup-range.webp” alt=”XLOOKUP with named ranges in Google Sheets \u2014 last_name range suggested as the lookup range” title_text=”Last_name range suggested as the lookup range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
7.<\/strong> For the third parameter, start typing the name of the column to return and select it like before.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-named-result-range.webp” alt=”XLOOKUP with named ranges in Google Sheets \u2014 amount range selected as the result range” title_text=”Amount range selected as the result range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
8.<\/strong> Close the parenthesis and press \u2018Enter\u2019 to see the result.<\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/xlookup-google-sheets-named-result.webp” alt=”XLOOKUP with named ranges in Google Sheets \u2014 amount $303.28 returned for Rennick” title_text=”Amount $303.28 returned for Rennick” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Show a default value when there’s no match<\/b><\/h3>\n
You can use XLOOKUP in Google Sheets to find missing values in a dataset by comparing two lists (e.g., a list of existing products and a list of new products).<\/p>\n
Let\u2019s look at an example.<\/p>\n
You’re a sales manager tracking employee performance in one Google Sheet. You have:<\/p>\n
\n- Sheet 1 (Employee Data): Contains employee names and ID numbers.<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/how_to_use_xlookup_in_google_sheets_39.webp” alt=”XLOOKUP in Google Sheets \u2014 Employee Data sheet with employee names and IDs” title_text=”Employee Data sheet with employee names and IDs” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n