CODEWITHSUNDEEP
arraysgoogle-sheetsindexinggoogle-sheets-formulamultiple-columns
Peter Meloche
Peter Meloche

Reputation: 5

Is there a way to INDEX / ARRAY certain multiple (non adjacent) columns from a range?

I have a calculated and filtered range that produces over a hundred columns. However I only want to show a handful.

=INDEX(FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1NLj6NBgU2z2V2ZKjr6-tRjITpYU9E5XOoklqTCEFrPo","Database!$B$2:$JT$1943")
,
(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1NLj6NBgU2z2V2ZKjr6-tRjITpYU9E5XOoklqTCEFrPo",Tracker!B15)=Tracker!B16)
+
(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1NLj6NBgU2z2V2ZKjr6-tRjITpYU9E5XOoklqTCEFrPo",Tracker!B15)=Tracker!B15))
,,7)

The formula looks pretty complicated and long but it's just a standard filter with two possible correct solutions - I'm just pulling the data from another sheet. It filters down over 1400 rows based on variables. Then out of that filtered range it indexes the selected column.

It currently only shows column 7, but I am looking for it show column 7, 255, 261, 256, 266, 262. In that order.

Is there a way to do this? I have had a look around and can't seem to find the Syntax. Happy to use solutions that change the original formula completely but please bear in mind, I am looking to use SORT function eventually on the whole INDEX formula.

Thanks in advance.

Example sheet to play around with;

https://docs.google.com/spreadsheets/d/1gj1E2lLPOz-kbGw4qB-fx9VhefN1vFbEiQHY57NaNSo/edit?usp=sharing

Upvotes: 0

Views: 694

Answers (1)

JPV
JPV

Reputation: 27262

In the tab 'JPV_HELP' I entered in cell C3

=IF($B$15="None","-",
QUERY(
IMPORTRANGE(
"https://docs.google.com/spreadsheets/d/1NLj6NBgU2z2V2ZKjr6-tRjITpYU9E5XOoklqTCEFrPo",
"Database!$B$2:$JT$1943"), 
"Select Col7, Col255, Col261, Col256, Col266 , Col262 
where Col266 matches '"&textjoin("|", 1, B15:B16)&"'", 1)
)

If you want to sort by a certain column you can include that in the query.

"Select Col7, Col255, Col261, Col256, Col266 , Col262 
where Col266 matches '"&textjoin("|", 1, B15:B16)&"' order by Col7 asc"

Use 'desc' instead of 'asc' of you want to sort descending.

See if that works for you?

Upvotes: 1

Related Questions