Home > Net >  Trying to query for cell above contains
Trying to query for cell above contains

Time:03-18

I have a column of data that contains names, and the name always appears above a cell which contains parentheses. I'm able to run a query that shows those cells

=query(A:A,"Select * where A contains '('",)

but it's the cells directly preceding them that I need. Any help would be appreciated.

CodePudding user response:

Try the following:

=index(hlookup("z",{"z";A:A},filter(row(A:A),regexmatch(A:A,"\(")),0))

enter image description here

You can replace the two occurrences of z with whatever you want.

The formula is self-explanatory if you are familiar with HLOOKUP and arrays.

CodePudding user response:

try:

=QUERY(A:A; "skipping 2")

or:

=QUERY(A:A; "where not A contains '('")
  • Related