CODEWITHSUNDEEP
google-sheets
Einarr
Einarr

Reputation: 332

Index query formula

Is it possible to index the query formula so that it outputs on the row where it matches much like Vlookup?

I need something other then Vlookup because it only matches on the first column, and can not use match index because it only returns the match and I need to return multiple columns

Thanks

=query(CouncilsLink!B:Q, "select B,D,F where D matches'"&TEXTJOIN("|",false,Elements!B1:B123)&"'")

Upvotes: 0

Views: 69

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

You can use an If statement with Regexmatch:

=ArrayFormula(if(regexmatch(CouncilsLink!D:D,TEXTJOIN("|",true,Elements!B1:B123)),{CouncilsLink!B:B,CouncilsLink!D:D,CouncilsLink!F:F},""))

Where CouncilsLink contains

enter image description here

Elements contains

enter image description here

and the result is

enter image description here

Or you could use Vlookup or Match like this

=ArrayFormula(if(iserror(vlookup(CouncilsLink!D:D,Elements!B:B,1,false)),"",{CouncilsLink!B:B,CouncilsLink!D:D,CouncilsLink!F:F}))

=ArrayFormula(if(isnumber(match(CouncilsLink!D:D,Elements!B:B,0)),{CouncilsLink!B:B,CouncilsLink!D:D,CouncilsLink!F:F},""))

Upvotes: 1

Arsalan Karamat
Arsalan Karamat

Reputation: 17

You can use this where Col1 and Col2 are columns you want to fetch and A3 is a column you want to match with 

=iferror(QUERY(IMPORTRANGE("SHeet ID","SheetName!StartColumn:EndCoumn"), "select Col1,Col2 where Col1="&$A3,0),"")

Upvotes: 1

Related Questions