Google Sheets Query Formula issues-CodePudding

Here's what I'm trying to do: I have two sheets in a single Google Spreadsheet, and I'm trying to pull values from [Sheet1:ColumnG] to every row in [Sheet2:ColumnF] WHEN data in its own row matches the value of [Sheet2:ColumnE].

I was trying to do that with a Query function:

=query(Sheet1!C:J, "select G where C matches '"&E2&"'", 0)

This formula works for ONE row at a time, but I can't figure out what value to put in the '"&E2&"' spot that will generate a result for every cell in the column. It'll be a PITA if I have to copy this formula on every row.

Is there a fix? Maybe it requires a script? Or is there a different formula that would produce the result I'm looking for?

CodePudding user response：

Try with VLOOKUP and INDEX (or ARRAYFORMULA)

=INDEX(IFERROR(VLOOKUP(E2:E,Sheet1!C:J,8, 0)))

CodePudding user response：

try:

=INDEX(XLOOKUP(E2:E,Sheet1!C:C,Sheet1!G:G,))

CodePudding user response：

works with yours as well:

=BYROW(E2:E, LAMBDA(e, QUERY(Sheet1!C:J, "select G where C matches '"&e&"'", 0))