We want to search List1 (column A) for the value stored in cell B2 and retrieve the row numbers that correspond with every successful match.
Use the SMALL, IF, and ROW functions as shown in the following Array formula:
Copy the formula down column C until 99999 is returned, which indicates that no further matches were found.
To apply Array formula:
Select the cell, press and simultaneously press .
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.