Get row# if containing designated character

C

caabyyc

Member
Joined
Dec 16, 2014
Messages
52
Reaction score
1
Points
8
Location
YYC/YVR/YXX
Excel Version(s)
O365
Source: A1:A5 , B1

A1: 00B0
A2: 00v000
A3: B009
A4: 00C0
A5: //ME

B1:A
B2:B

use formula in column C (start from C1 vertically) to get row# of A1:A5 if they contain character in B1 or B2 , in example, the result should be C1=1,C2=3,c3="",c4=""

thanks !!
 
Have tried a few things, but no luck with a formula. Main problems are that you have duplicate instances (e.g. A1 and A3), and its not clear how much data you have altogether in rows A and B.
How would you feel about using a VBA module, or a UDF?
 
Here is a formula solution that should work:

=IFERROR(SMALL(IF(ISNUMBER((SEARCH(TRANSPOSE(INDIRECT("B1:B2")),$A$1:$A$5))),ROW($A$1:$A$5)-ROW($A$1)+1),ROWS($C$1:$C1)),"")

This is an Array formula and so must be confirmed with CTRL+SHIFT+ENTER not just ENTER. Then copied down
 
Sure does! I didn't realise that you could match pairs from one array with singles in a second array as you did here. I presume its because you've got Excel to build the 10 element one rather than supplying it.
Also, the amount of data isn't really an issue, within reason of course. Brilliant.
 
it works perfectly ! thanks !

NBVC said:
Here is a formula solution that should work:

=IFERROR(SMALL(IF(ISNUMBER((SEARCH(TRANSPOSE(INDIRECT("B1:B2")),$A$1:$A$5))),ROW($A$1:$A$5)-ROW($A$1)+1),ROWS($C$1:$C1)),"")

This is an Array formula and so must be confirmed with CTRL+SHIFT+ENTER not just ENTER. Then copied down
Click to expand...
 
Still thanks for your effort. I would rather using helper column if possible .

Hercules1946 said:
Have tried a few things, but no luck with a formula. Main problems are that you have duplicate instances (e.g. A1 and A3), and its not clear how much data you have altogether in rows A and B.
How would you feel about using a VBA module, or a UDF?
Click to expand...
 
In this case, I wasn't quite able to find a workable formula, but the one posted by NBVC will work fine on its own. :)
 
I'm sensing this is probably a school or course assignment....
 
You must log in or register to reply here.
Back
Top