Search code examples
google-sheetsgoogle-sheets-formula

Google Sheets query/function to search multiple criteria, including an offset

I have a google sheet that I've set up as an audit log. It records changes to a database sheet, one row for every set of edits (that are made via a google app script). For example, the audit log might look like this:

audit log

For a given row, the log records the ID (1, 2, etc.) and only the fields that were updated for that ID, with the field name preceding the field value (e.g. field_1 with updated value ttt for ID 1).

I want to query the audit log table to get data into this formatted table:

desired output

The query/function would search the audit log for the row with the ID (e.g. 1) and fill in the data values that exist in that row in the right columns (e.g. field_1 = ttt; field_2 = rrr).

Here is the google sheet with this example in it. I'm able to find the value in the column to the right of the field name by using an index-match with 1 added to the column. But I can't figure out how to combine that with finding the right row.

Solution

  • Give a try on the following formula-

    =IFERROR(LET(x,FILTER($B$3:$G$4,$A$3:$A$4=$A11),INDEX(x,1,MATCH(B$10,x,0)+1)),"")

    To make the formula dynamic array try-

    =MAKEARRAY(
ROWS(A11:A12),COLUMNS(B10:G10),
LAMBDA(r,c,
IFERROR(LET(x,FILTER($B$3:$G$4,$A$3:$A$4=INDEX(A11:A12,r)),
INDEX(x,1,MATCH(INDEX(B10:G10,1,c),x,0)+1)),"")
))

    enter image description here