google-sheetslambdaarray-formulas

Row and Column Reference from Google Query using array functions


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?

Updated Information

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:

  • Vlookup of SKU ID to another table, and replace SKU ID with looked up value
  • Apply EOMONTH function to Date
  • No transformations on Name column

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)


Solution

  • 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}))))
    

    enter image description here