I want to search for values greater than a specific number (e.g. 0) and list down adjacent cell valu-CodePudding

I have two columns of data with 'vehicle count' in first column and 'travel time' in second column. I want to search for rows with vehicle count greater than a specific number (e.g. 0) and list down adjacent cell values from the 'travel time' column. Data Sample

CodePudding user response：

You need FILTER() function like-

=FILTER(B:B,A:A>0)

Edit: For Excel-2019 use below formula.

=IFERROR(INDEX($B$1:$B$100,AGGREGATE(15,6,ROW($1:$100)/($A$1:$A$100>0),ROW($A1))),"")

Edit2: As per attached screenshot use below formula-

=IFERROR(INDEX($C$4:$C$27,AGGREGATE(15,6,(ROW(B$4:$B$27)-ROW($B$3))/($B$4:$B$27>0),ROW(1:1))),"")