what does equality to a range mean in excel macros?

Question

I have to update this code to do a substring or regex match but I don't even understand the code:

INDEX(
   $DATA.B$2:B$1501;
   SMALL(
      IF(
         $DATA.$A$2:$A$1501=$B$3;
         ROW($DATA.$A$2:$A$1501)-ROW($DATA.$A$2)+1
      );
      ROWS($DATA.$A$1:$A1)
   )
)

The bit that has me scratching my head the most is $DATA.$A$2:$A$1501=$B$3 what does it mean to test equality of a single cell with a cell range?

In addition, this code is comparing with a string in $B$3, how can I change this to a regex or substring match so that the test is true when $B$3 matches the start of the value in $DATA.$A$2:$A$1501?

Answer 1

To check what does a range return, I did the following.

Filled the cell A1 to F1 with values A,B,C,D,E,F
Placed the cursor on Cell B4
Typed in the formula =A1:F1, it returned B
Placed the cursor on Cell C4
Typed in the formula =A1:F1, it returned C

EDIT: So, if you put value C in cell C3 and put the formula in C4 (=A1:F1=C3), it will return true.

So, I guess the formula gets the value for the current column in the given a range.
Could you give an example by screenshots as to what you want to do?

Answer 2

To generalize a little from the answer given, if you do an equality test between an array and a scalar, you get back an array of boolean values. That's why you needed to treat the whole formula as an array formula, because it was manipulating arrays.