|It's not clear to me where you want the results to be placed. When you say "How do I get it to return all the numbers in col A where there is a match" it sounds like you want a single formula to return multiple values. That won't work.|
I'll assume you want the first value for the first match to be in B2, the next in B3, etc.
As you probably already know, your formula is only going to return the first MATCH it finds. Therefore, we need to change what it is looking for by creating unique strings for each of the values in BY2:BY12444. To do that we'll use a Helper Column.
Let's say you have this:
In BX2 enter this formula and drag it down to BX12444
You should now have this:
2 Tom 1 Tom
3 Fred 1 Fred
4 Sue 1 Sue
5 Fred 2 Fred
We now have unique values for the MATCH function to find.
Now, in B2 place this modified version of your formula and drag it down.
The ROW() function simply returns the number of the Row it is placed in, so =ROW()-1 will return 1 in Row 2, 2 in Row 3, etc.
Note that we are now looking for a MATCH in Column BX and what we are matching is Tom 1, Tom 2, etc. not just Tom. Therefore you'll get every match for the values in Column BY.
Once you drag it down past the number of matches, it will return #N/A, so wrap the formula in an IF(ISNA(MATCH(...) function and you'll be good to go.
=IF(ISNA(MATCH($I$2&" "&ROW()-1,Data!$BX$2:$BX$12444,0)),"",INDEX(Data!$A$2:$A$12444,MATCH($I$2&" "&ROW()-1,Data!$BX$2:$BX$12444,0)))
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.