0

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Alasdair Grant
  • 3
  • 2
  • Your question doesn't have enough details. How do we know which column header to look for? – Gangula Oct 15 '21 at 20:45
  • Sorry, For example I would be looking for a count of a certain log. The logs are column headers, and the departments and individuals make up the rows – Alasdair Grant Oct 15 '21 at 20:50
  • that still doesn't give clarity. Based on what you said, you can use `MATCH("certianlogName", logColumnHeaders, 0)`. But this info will most likely be insufficient for you. Please add some sample data to the question so that it can used as reference. – Gangula Oct 15 '21 at 20:52
  • 1
    I've not explained it very well sorry, I have made this mock up to try and help it make sense. https://docs.google.com/spreadsheets/d/1Uh7yg3X70UH2oT2Pm-0SbLZeIyMgA9ELEPQ8nhbv07Y/edit?usp=sharing – Alasdair Grant Oct 15 '21 at 20:58
  • I've put an example on there as well of how it should return the results – Alasdair Grant Oct 15 '21 at 21:01

3 Answers3

0

You can try:

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

enter image description here

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

basic
  • 11,673
  • 2
  • 9
  • 26
  • I am getting this error message Error Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 1096. Actual: 1095. – Alasdair Grant Oct 15 '21 at 21:34
  • =QUERY({Year!A2:A1097,Year!F3:F1097,INDEX(A:FT,0,match(C67,1:1,0))},"SELECT * WHERE Col 6 ='" & LOWER(B1) & "' ") This is what I have entered – Alasdair Grant Oct 15 '21 at 21:35
  • 1
    @AlasdairGrant As I said, you have trailing spaces in some cells. Check if they are also in the real data. – basic Oct 15 '21 at 21:51
  • So this is an exact copy of how the spreadsheet looks. https://docs.google.com/spreadsheets/d/1esXu94YYGorFBWUodlm5m4WDXYpkTLOr3WftgWhevx8/edit?usp=sharing I can get it to work on the original example I shared but I cant get it to work on this. I've checked for spaces. Am I making a silly mistake somewhere? Thankyou for your help I really apricate it – Alasdair Grant Oct 16 '21 at 18:01
  • @AlasdairGrant For your dataset working formula will be: ```=QUERY({Data!A:F,index(Data!A:FT,0,MATCH(B2,Data!1:1,0))},"SELECT * WHERE Col6 ='" & A2 & "'")``` – basic Oct 16 '21 at 18:54
  • Okay! So, when I do that on the example sheet which matches the same format as the actual sheet it works perfectly! Thank you so much! However, when I put it in the real sheet I get this error https://drive.google.com/file/d/1j6ZsnAeNPVSaWbw9v6qF-mOlTOWbrF8g/view?usp=sharing =QUERY({Year!A:F,index(Year!A:FT,0,MATCH(B67,Year!1:1,0))},"SELECT * WHERE Col6 ='" & B1 & "'") Formula used – Alasdair Grant Oct 16 '21 at 20:29
  • @AlasdairGrant This error means that ```INDEX``` / ```MATCH``` does not return the data you are looking for. Without seeing this data, I will not be able to give a reason. There it is necessary to check whether the ranges are correct, whether the search value in B67 corresponds to one of the names in the first row, or whether there is no space in the names in the first row or in search value. – basic Oct 16 '21 at 20:44
  • You also can try to use ```TRIM``` function in the ```MATCH```: ```MATCH(TRIM(B67),TRIM(Year!1:1),0)``` – basic Oct 16 '21 at 20:46
  • Doh! I'd referenced the the wrong cell! You're an absolute legend! It works now. I really couldn't have done it without you! – Alasdair Grant Oct 16 '21 at 21:02
0

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
)

enter image description here

kishkin
  • 5,152
  • 1
  • 26
  • 40
0

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

=QUERY('Sheet Name'!A1:G20,"SELECT A, B, "&CHAR(MATCH("Log 4",'Sheet Name'!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('Sheet Name'!A1:G20,"SELECT A, B, `" & =REGEXEXTRACT(ADDRESS(1, MATCH("Log 4",'Sheet Name'!A1:G1), 4), "\D+") & "` WHERE B='w'")

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

  • the CHAR(MATCH...)) gets the column name of the desired Log
  • you can then use the column name to include that column in Query select statement

In the MATCH formula, you can also dynamically Match for a cell reference instead of specifying "Log 4" manually

Note: This is basically splitting the Query formula and concatenating it with another formula. So the &, ' and " symbols are really important

Sample Screenshot: Screenshot of output

Gangula
  • 5,193
  • 4
  • 30
  • 59
  • 1
    Hope he won't need to return anything from column 77. :) – kishkin Oct 16 '21 at 07:22
  • Good point kishkin, this only works till Column Z – Gangula Oct 16 '21 at 07:25
  • Ah! right, there's `CHAR`. I was actually joking about `BY` column which breaks the query as it is a keyword. – kishkin Oct 16 '21 at 07:34
  • 1
    You can replace `CHAR(N) + 64` with `REGEXEXTRACT(ADDRESS(1, N, 4), "\D+")` to go beyond `Z`. But `BY` will still break it. – kishkin Oct 16 '21 at 07:39
  • Oh, that's interesting. And I had faced issue with column `BY` to, but you can work-around it by using backtick like this ` `BY` ` – Gangula Oct 16 '21 at 07:46
  • I still cant get it to to work. I am trying to do the query on a different sheet to the data is on. Would that affect the formula working? – Alasdair Grant Oct 16 '21 at 10:50
  • Yes, that will affect the formula, you need to add references accordingly like `'SheetName'!A1:G20` and `'SheetName'!A1:G1` – Gangula Oct 16 '21 at 18:03