Google Spreadsheet, How to search an entire sheet for same matches and return values located to the-CodePudding

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:

CodePudding user response：

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)

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)