So I have this formula currently

=IFERROR(IF(MATCH(C1,test7!$B3:$B10,0),"1",),"0")

that corresponds to return a "1" or "0" value on a chart. But because the data is in different ranges some are between B3:B10 some are B13:B21 etc, I couldn't find a formula which will correlate with the IFERROR formula to return a 1 or 0. There would always be a 2 row gap between the ranges, eg B3:B10, B13:B21, B24:B40 etc.

Question is how do I incorporate the formula above to search and return "1" and "0" if my data has a two row gap and at random positions?

Sorry if it was explained poorly, it's my first time trying to do this on excel! I greatly appreciate all the help <3

CodePudding user response：

Give a try on below formula.

=--ISNUMBER(MATCH(E$1,INDEX($B$2:$B$19,MATCH($D2,$A$2:$A$19,0)):INDEX($B$2:$B$19,IFERROR(MATCH($D2 1,$A$2:$A$19,0),ROW($B$19)-ROW($B$1))),0))

Edit: If your user id is not sequential number or it is text then can use below formula.

=LET(x,SCAN("",$A$2:$A$19,LAMBDA(a,b,IF(b="",a&b,b))),y,$B$2:$B$19,z,FILTER(y,x=$D2),--ISNUMBER(XMATCH(E$1,z,0)))