First off, this board has been extremely useful to me, but there is something I need to figure out which I could not find through searching the forum so I'm posting my first thread. Here goes:
I am trying to identify the row(s) where a match occurs when there can be multiple occurrences of the match.
<tbody>
</tbody>
The first test I ran in cell C1 with the information above was to determine if "Brown" was located in the range of A1:A5 using formula:
=IF(ISNUMBER(MATCH("*"&B1&"*",A1:A5,0))=TRUE,"True","False")
In the case of the word "Brown" it occurs in the range of A1:A5 three times, so the result of the above formula would be "True"
Now what I would like to do is return the locations, in this case the row number(s), where the word brown is contained range of A1:A5 because in the case of the word Brown, it occurs three separate times in the range of A1:A5. I would like the resulting value of the formula in this case to be "1, 3, 5" indicating the word Brown occurs in rows 1, 3, and 5. The formula also needs to work in case there is only one match as well.
I tried using the below array formula:
={MATCH(FALSE,ISERROR(SEARCH(B1,'Working List of Vendors'!$A$1:$A$5)),0)} , but it would only return the first occurrence of the match which in this case would be row 1, or "1"
Obviously, this is just a small example of the data set I am using but if I can get the formula figured out for this small sample I can apply it to what I am working with.
Many Thanks
I am trying to identify the row(s) where a match occurs when there can be multiple occurrences of the match.
A | B | C | |
1 | john brown | Brown | True |
2 | Cathy Smith | ||
3 | Brown excavating company | ||
4 | XYZ Corp | ||
5 | Brown Advisors Inc. |
<tbody>
</tbody>
The first test I ran in cell C1 with the information above was to determine if "Brown" was located in the range of A1:A5 using formula:
=IF(ISNUMBER(MATCH("*"&B1&"*",A1:A5,0))=TRUE,"True","False")
In the case of the word "Brown" it occurs in the range of A1:A5 three times, so the result of the above formula would be "True"
Now what I would like to do is return the locations, in this case the row number(s), where the word brown is contained range of A1:A5 because in the case of the word Brown, it occurs three separate times in the range of A1:A5. I would like the resulting value of the formula in this case to be "1, 3, 5" indicating the word Brown occurs in rows 1, 3, and 5. The formula also needs to work in case there is only one match as well.
I tried using the below array formula:
={MATCH(FALSE,ISERROR(SEARCH(B1,'Working List of Vendors'!$A$1:$A$5)),0)} , but it would only return the first occurrence of the match which in this case would be row 1, or "1"
Obviously, this is just a small example of the data set I am using but if I can get the formula figured out for this small sample I can apply it to what I am working with.
Many Thanks