Change this specific part of the formula
XLOOKUP($A2 & B$1,
to
XLOOKUP($A2:A & B$1,
so that entire range of A2:A is considered
Reddit
reddit.com › r/googlesheets › is it possible to use arrayformula with xlookup without using the same lookup and result range for each row?
r/googlesheets on Reddit: Is it possible to use ARRAYFORMULA with XLOOKUP without using the same lookup and result range for each row?
September 17, 2022 -
Basically, I have a sheet that has some data where each row is in the following format:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | a | b | c | 1 | 2 | 3 | <a/b/c> | ?<1/2/3> |
| 2 | d | e | f | 4 | 5 | 6 | <d/e/f> | ?<4/5/6> |
The first three and second three columns correspond with each other (a matches to 1, b matches to 2, c to 3). Column G will be one of the values in columns A-C and I want column H (the ?) to be the corresponding value from columns D-F.
I was able to get this working on for a single row at a time using
=XLOOKUP(G1, A1:C1, D1:F1)
But this would require me to manually extend the formula down to each row I want to apply it to. So I was looking into using ARRAYFORMULA in order to automatically generate this value as new rows are added to the sheet. I tried doing this with something like this:
=ARRAYFORMULA(XLOOKUP(G1:G, A1:C1, D1:F1))
However, it seems to do the lookups against the cells in row 1 every time, rather than doing it on the next row corresponding to the current iteration of G that is being calculated.
Does anyone know if there is any way to have the lookup/result ranges in the XLOOKUP automatically match the row that is being calculated by the ARRAYFORMULA? Or maybe I'm going about this the wrong way, in which case any advice would be appreciated!
Top answer 1 of 4
2
Simply flatten:
=ARRAYFORMULA(ARRAY_CONSTRAIN(XLOOKUP(G1:G, FLATTEN(A1:C),FLATTEN(D1:F)) ,COUNTA(G1:G),1)) 2 of 4
1
Try adding IF(A:A<>"", XLOOKUP, "") before your array.
XLOOKUP function with multiple criteria - usual boolean logic not working? Dec 14, 2025
r/googlesheets 9mo ago
Using Arrayformula to pull a specific value from multiple sheets based on two values Oct 30, 2025
r/googlesheets 10mo ago
Coupler.io Blog
blog.coupler.io › home › xlookup google sheets explained with 9 examples
XLOOKUP Google Sheets Guide (With Examples) | Coupler.io Blog
August 5, 2025 - XLOOKUP can work with arrays natively, opening up the doors to more advanced calculations · We’ll share various examples of simple and more advanced usage of Google Sheets XLOOKUP in the following chapters. The formula for XLOOKUP in Google Sheets is as follows:
google sheets - Is there a way to return multiple columns with XLOOKUP within an ARRAYFORMULA? - Web Applications Stack Exchange
I'm using XLOOKUP to return a row with 4 columns. At the moment I have to copy/paste the formula every time new rows are inserted, so I thought of using ARRAYFORMULA to skip this step. The problem is More on webapps.stackexchange.com
XLOOKUP and ARRAYFORMULA, is there a better way? - Google Docs Editors Community
Skip to main content · Google Docs Editors Help · Sign in · Google Help · Help Center · Community · Google Docs Editors · Terms of Service · Submit feedback · Send feedback on More on support.google.com
Arrayformula Xlookup - Google Docs Editors Community
Skip to main content · Google Docs Editors Help · Sign in · Google Help · Help Center · Community · Google Docs Editors · Terms of Service · Submit feedback · Send feedback on More on support.google.com
Google Sheets - Array Formula with Xlookup and Importrange - Stack Overflow
I have a Sheet which basically adds name and address info from a membership list to a list of bank payments. LINK TO EXAMPLE SHEET It uses an Xlookup (in Column G) to compare the Bank Account Name ( More on stackoverflow.com
Does Coefficient work with both Google Sheets and Excel?
Yes, Coefficient supports both Google Sheets and Microsoft Excel. You can install Coefficient from the Google Workspace Marketplace for Sheets or from Microsoft AppSource for Excel.
coefficient.io
coefficient.io › home
Ultimate Guide to XLOOKUP in Google Sheets (Updated August 2025)
What is Coefficient's AI Sheets Assistant?
AI Sheets Assistant is Coefficient's AI-powered feature that helps you build formulas, create charts, generate pivot tables, and build entire dashboards using natural language commands in Google Sheets. Unlike generic AI assistants, it's context-aware and creates native Google Sheets objects that are live and editable. It understands your spreadsheet's layout and data structure to provide accurate, actionable results.
coefficient.io
coefficient.io › home
Ultimate Guide to XLOOKUP in Google Sheets (Updated August 2025)
How does Coefficient's automated refresh work?
Coefficient allows you to schedule automatic data refreshes at intervals you choose: hourly (1, 2, 4, or 8 hours), daily, weekly, or monthly. Once scheduled, your spreadsheet data automatically updates from your connected systems without any manual work. You can also trigger manual refreshes anytime with an on-sheet button or through the sidebar.
coefficient.io
coefficient.io › home
Ultimate Guide to XLOOKUP in Google Sheets (Updated August 2025)
00:56
How to do an XLOOKUP in Excel or Google Sheets - 12 Days of ...
🚨This is an ELITE formula combination 🤯 This powerful combo unlocks new levels of efficiency in Google Sheets. Combine ARRAYFORMULA and XLOOKUP to automate lookups, streamline data retrieval, and perform complex calculations with ease. Say goodbye to manual work and hello to spreadsheet mastery! #GoogleSheets #Spreadsheethack #ARRAYFORMULA #XLOOKUP #FormulaCombinations #ProductivityHacks #spreadsheetautomation | TikTok
07:33
XLOOKUP Returns Multiple Columns in Excel & Google Sheets - YouTube
15:01
How to Use XLOOKUP in Google Sheets - YouTube
Top answer 1 of 2
6
Use map(), like this:
=map(A2:A, lambda(a, xlookup(a, Sheet2!A2:A, Sheet2!D2:G)))
See map() and lambda().
2 of 2
3
Further to doubleunary's answer, the reason why xlookup doesn't work in the expected way with arrayformula is because, as per the documentation: 'If result_range is more than one row or column, then the output will be the entire row/column at the index a match was found in the lookup_range', i.e. it returns an array by default, so the desired arrayformula result would be an array-of-arrays which isn't supported by arrayformula. Doubleunary's answer is effectively generating an array-of-arrays using map which does allow this.
You could also try =arrayformula(vlookup(A2:A,{Sheet2!A2:A,Sheet2!D2:G},{2,3,4,5},0)) which works because vlookup does not return arrays by default so arrayformula works on both the search_key and index arguments to return a contiguous 2D array (which is allowed).
Google Support
support.google.com › docs › thread › 247135855 › xlookup-and-arrayformula-is-there-a-better-way
XLOOKUP and ARRAYFORMULA, is there a better way? - Google Docs Editors Community
Skip to main content · Google Docs Editors Help · Sign in · Google Help · Help Center · Community · Google Docs Editors · Terms of Service · Submit feedback · Send feedback on
Ben Collins
benlcollins.com › home › xlookup function in google sheets
XLOOKUP Function in Google Sheets
February 7, 2024 - This is in position 3 of the lookup array, so it returns the value from the 3rd position of the results array, i.e. 1.25%. One final thing to mention with this example, notice how the fourth argument is blank. This is where we can specify a “missing value” for when no match is found. However, it’s not required here because we’re using an approximate match anyway. XLOOKUP in Google Sheets supports three wildcards, *, ?, and ~.
Google Support
support.google.com › docs › thread › 208525219 › arrayformula-xlookup
Arrayformula Xlookup - Google Docs Editors Community
March 29, 2023 - Skip to main content · Google Docs Editors Help · Sign in · Google Help · Help Center · Community · Google Docs Editors · Terms of Service · Submit feedback · Send feedback on
Sheetgo
sheetgo.com › home › how to use xlookup in google sheets? (+ formulas and examples)
How to use XLOOKUP in Google Sheets? | Sheetgo
June 3, 2025 - You want to see each student’s score next to their name in the “Students” sheet. Let’s choose John Doe. In the cell under “Test Score” for John Doe, use this XLOOKUP formula: =XLOOKUP(A2, ‘Test Scores’!A:A, ‘Test Scores’!B:B, “Not Found”) ... Lookup Value (A2): The ID number you’re looking for. Lookup Array (‘Test Scores’!A:A): This is the column in the “Test Scores” sheet where the XLOOKUP function should find the ID numbers.
Coefficient
coefficient.io › home
Ultimate Guide to XLOOKUP in Google Sheets (Updated August 2025)
September 2, 2025 - The best way to do this in XLOOKUP formulas is use a simple Array Formula like this: Now let’s say you would like to search for a First Name and return all of the values from the same row. The obvious way to do this might be to have multiple VLOOKUP or XLOOKUP formulas, each returning one value. But XLOOKUP actually makes this much easier. You can do this all in one formula by specifying a Result Range that is multiple columns: You can combine the Google Sheets INDEX and MATCH functions to simulate the functionality of XLOOKUP.
Stack Overflow
stackoverflow.com › questions › 75426005 › google-sheets-arrayformula-xlookup
array formulas - Google Sheets - ArrayFormula - XLOOKUP - Stack Overflow
February 12, 2023 - so I have this situation: I have dates (mmm-yy) in one row (horizontal) and other dates in one column (vertical) I'm trying to match them and if the dates match, then ArrayFormula-XLOOKUP but since the result cell may overwrite the next cell with formula, I've got Circular dependency detected.enter image description here
InfoInspired
infoinspired.com › home › google docs › spreadsheet › xlookup in google sheets: complete guide
XLOOKUP in Google Sheets: Complete Guide
2 weeks ago - This tutorial covered the key features you need to master XLOOKUP in Google Sheets. It explained all six arguments with practical examples and covered both common and advanced techniques, including vertical and horizontal lookups, approximate and wildcard matching, reverse and binary searches, array lookups, structured table references, two-way lookups, searching across multiple columns, and creating dynamic ranges.
Google Support
support.google.com › docs › thread › 206605532 › arrayformula-with-xlookup-and-returning-2-cols-of-information
Arrayformula with Xlookup and Returning 2 Cols of Information - Google Docs Editors Community
March 17, 2023 - Skip to main content · Google Docs Editors Help · Sign in · Google Help · Help Center · Community · Google Docs Editors · Terms of Service · Submit feedback · Send feedback on
Forms.app
forms.app › home › blog › data collection & analytics › how to use xlookup function in google sheets
How to use XLOOKUP function in Google Sheets
March 22, 2024 - Yes, XLOOKUP can handle two criteria when you pair it with functions like FILTER or use it in an array formula. This allows you to perform more complex searches that match both conditions you specify.
Google Support
support.google.com › docs › thread › 234943537 › add-array-to-an-xlookup-formula
Add array to an xlookup formula. - Google Docs Editors Community
September 14, 2023 - Skip to main content · Google Docs Editors Help · Sign in · Google Help · Help Center · Community · Google Docs Editors · Terms of Service · Submit feedback · Send feedback on
Spreadsheet Point
spreadsheetpoint.com › home › google sheets tips › the ultimate guide to the xlookup google sheets function
Guide to the XLOOKUP Google Sheets Function [2026] | SSP
August 18, 2023 - Here’s how to use Google Sheets ... and add the ampersand sign (&), then select the second lookup value. Add a comma and enter the ARRAY FORMULA....
Top answer 1 of 2
14
It is usually easier to use filter(), like this:
=filter('Master Data'!D2:D9, C3 = 'Master Data'!A2:A9, C4 = 'Master Data'!C2:C9)
To answer the question, to do the same with xlookup(), you can combine columns into a compound key, as attempted by the formula in the question. To combine columns of multiple rows with the & operator, you need to wrap the expression in arrayformula(), like this:
=xlookup(
C3 & "→" & C4,
arrayformula('Master Data'!A2:A9 & "→" & 'Master Data'!C2:C9),
'Master Data'!D2:D9
)
See your sample spreadsheet.
2 of 2
2
you can even use the Multiple Criteria Boolean Expressions XLOOKUP functionality
=XLOOKUP(1,ARRAYFORMULA(('Master Data'!A2:A9=C3)
*('Master Data'!$C$2:$C$9=C4)),'Master Data'!D2:D9,,0)
Here is a simple example:
Here is the formula associated with the previous example:
=XLOOKUP(1,ARRAYFORMULA((A2:A3=F2) * (B2:B3=F3)),C2:C3)
The formula can be adapted from Excel, check: XLOOKUP with multiple criteria.
Remember that XLOOKUP finds the first match, if you have multiple matches, you need to join the result for example.
Google Support
support.google.com › docs › thread › 201613378 › lookup-using-arrayformula
Lookup using arrayformula - Google Docs Editors Community
Skip to main content · Google Docs Editors Help · Sign in · Google Help · Help Center · Community · Google Docs Editors · Terms of Service · Submit feedback · Send feedback on
Sheetgo
sheetgo.com › home › mastering the xlookup formula: 4 use cases with real-life examples
Mastering the XLOOKUP Formula: 4 Use Cases with Real-Life Examples - Sheetgo
June 3, 2025 - =ArrayFormula(XLOOKUP($F$5:$F$14, $A$4:$A$14, $B$4:$B$14 & ” ” & $C$4:$C$14,””)) ... ArrayFormula: Ensures the formula works across multiple rows dynamically without dragging it down.
Google Workspace
workspace.google.com › marketplace › app › xlookup_for_sheets › 319859325063
Xlookup for Sheets - Google Workspace Marketplace
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.