Search code examples
sortinggoogle-sheetsfiltergoogle-sheets-formulagoogle-query-language

Google Spreadsheet, How to search an entire sheet for same matches and return values located to the right of them?


In Sheet1 I have many values that are the same, for example the name "boland1" exists in many places in Sheet1 and although the content is stuctured, the positions vary. Each "boland1" have different values to the right of them, which is what I want to extract.

I am looking for a formula that searches through that entire first sheet and returns the values located to the right of each "boland1". I do not want the formula to choose from specific columns, I already know how to do that. I want the entire sheet to be the lookup range, is this possible?

Here is a spreadsheet example, look at the sheet names for info and also look at Sheet2 for more clarification on what I am looking for: https://docs.google.com/spreadsheets/d/1W8uVmyLS9O907v8Ony-rfbhDuHJqAqsP_eH3Gj1PjUg/edit?usp=sharing

Here are some pictures from the spreadsheet for the people that don't want to click on links: enter image description here enter image description here


Solution

  • use:

    =QUERY({
     FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-1; 6)=0))\ 
     FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-2; 6)=0))\ 
     FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-3; 6)=0))\ 
     FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-4; 6)=0))\ 
     FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-5; 6)=0))}; 
     "where Col1 = 'Boland1'"; 0)
    

    enter image description here


    update:

    =QUERY(SORT({
     FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-1; 6)=0))\
     FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-2; 6)=0))\ 
     FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-3; 6)=0))\ 
     FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-4; 6)=0))\ 
     FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-5; 6)=0))}; 
     FLATTEN(FILTER(COLUMN(Sheet1!A2:1000)*SEQUENCE(ROWS(Sheet1!A2:A); 
     COLUMNS(Sheet1!A2:1000); 1; 0); MOD(COLUMN(Sheet1!A2:2)-1; 6)=0)); 1); 
     "where Col1 = 'Boland1'"; 0)