Is it possible for an array function such as SCAN, MAP or REDUCE to be aware of the column or row reference for Query Arrays?
Example:
Consider this data - which exists in cell reference A1:B2
A | B |
---|---|
John | Smith |
Paul | Jones |
If we apply the below formula to the range:
=map(A1:B2, lambda(x, row(x) & "-" & col(x))
We would get the below result
A | B |
---|---|
1-1 | 1-2 |
2-1 | 2-2 |
However, if we do the following...
Instead of referencing the Range directly, we reference it via a query, eg.
=map(query(A1:B2), lambda(x, row(x) & "-" & col(x))
We get an error:
"Argument must be a range."
I understand why this is happening, but I would like to know how can we get the row and column reference back in these cases?
Assume we had the following array (as a result from a google query):
SKU ID | Date | Name |
---|---|---|
1 | 2-Feb-2023 | John |
2 | 3-Feb-2023 | Harry |
3 | 4-Feb-2023 | Paul |
Using the MAP function, how could we transform the data so that:
using MAP, if we knew the row number and column number for each entry on the table, then you would be able to apply the correct transformation to each entry and ignore the header and column 3 (name)
Here's one approach you may test out:
=let(Σ,query({A1:C4}), Λ,chooserows(Σ,sequence(rows(Σ)-1,1,2)),
vstack(chooserows(Σ,1),
map(choosecols(Λ,1),choosecols(Λ,2),choosecols(Λ,3),lambda(sku,date,name,{vlookup(sku,A:A,1,),eomonth(date,),name}))))