Use map(), like this:
=map(A2:A, lambda(a, xlookup(a, Sheet2!A2:A, Sheet2!D2:G)))
See map() and lambda().
Answer from doubleunary on Stack ExchangeBasically, 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!
Simply flatten:
=ARRAYFORMULA(ARRAY_CONSTRAIN(XLOOKUP(G1:G, FLATTEN(A1:C),FLATTEN(D1:F)) ,COUNTA(G1:G),1))Try adding IF(A:A<>"", XLOOKUP, "") before your array.
XLOOKUP and ARRAYFORMULA, is there a better way? - Google Docs Editors Community
Google Sheets - Array Formula with Xlookup and Importrange - Stack Overflow
Arrayformula Xlookup - Google Docs Editors Community
google sheets - How do I apply ARRAY Formula for XLOOKUP? - Stack Overflow
Use map(), like this:
=map(A2:A, lambda(a, xlookup(a, Sheet2!A2:A, Sheet2!D2:G)))
See map() and lambda().
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).
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.
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.