search for a specific Column and then lookup the value of the matching row based off a name-CodePudding

I am trying to get a google sheet to search for a specific cell in a table. The headers change so it might be A6 one week and then A9 the other and so on.

Once it's found that row, I want it to search and pull all of that departments names and data for the column its matched with.

I am 23 sheets in and my heads hit a brick wall and I just can figure it out.

CodePudding user response：

You can try:

=QUERY({A:B,INDEX(A:G,0,MATCH(D25,1:1,0))},"SELECT * WHERE Col2='" & LOWER(F25) & "'")

Note - you should remove unnecessary spaces. In sample data, they were in cells C1 and D25.

CodePudding user response：

Try this:

=QUERY(
  FILTER(
    IFS(
      TRIM(1:20) = "", 0,
      ISNUMBER(1:20), 1:20,
      True, LOWER(TRIM(1:20))
    ),
    1:1 <> ""
  ),
  "SELECT Col1, Col2, Col" & MATCH(TRIM(D25), ARRAYFORMULA(TRIM(1:1)),) & "
   WHERE Col2 = '" & LOWER(F25) & "'",
  1
)

CodePudding user response：

You can use a combination of CHAR(MATCH...)) and Query formula to get this

=QUERY(A1:G20,"SELECT A, B, "&CHAR(MATCH("Log 4",A1:G1) 64)&" WHERE B='w'")

Above formula only works till column Z, but thanks to Kishkin's comment below, you can use it beyond Z like this:

=QUERY(A1:G20,"SELECT A, B, `" & =REGEXEXTRACT(ADDRESS(1, MATCH("Log 4",A1:G1), 4), "\D ") & "` WHERE B='w'")

You use SUBSTITUTE instead of REGEXTRACT too. You can refer to