What formula?

[brooklyn2007]

Hello guys. I have this problem. Lets say:

in cell A1 is this text "John-Brown"
in cell A2 is this text "Jim-White"
in cell A3 is this text "George-Red"


In cell B1 I have "John"
In cell B2 I have "Jim"
In cell B3 I have "George"

I need a formula in cell C1 that based on cell B1 will display me the value in A1 and so on for cell C2, C3. I know that by using a formula like this:

=IF(LEFT(A1,LEN(B1))=B1,A1,"")

it will give me the result but the thing is that I have a large set of data in my real life and the values I'm looking are in different cells in the column. It looks to me that should be some kind of VLOOKUP formula or some sort of INDEX and MATCH to come to the solution of this problem but I can't figure it out.

Thx in advance

 

[Aladin Akyurek]

Hello guys. I have this problem. Lets say:

in cell A1 is this text "John-Brown"
in cell A2 is this text "Jim-White"
in cell A3 is this text "George-Red"


In cell B1 I have "John"
In cell B2 I have "Jim"
In cell B3 I have "George"

I need a formula in cell C1 that based on cell B1 will display me the value in A1 and so on for cell C2, C3. I know that by using a formula like this:

=IF(LEFT(A1,LEN(B1))=B1,A1,"")

it will give me the result but the thing is that I have a large set of data in my real life and the values I'm looking are in different cells in the column. It looks to me that should be some kind of VLOOKUP formula or some sort of INDEX and MATCH to come to the solution of this problem but I can't figure it out.

Thx in advance

C1, copy down:

=INDEX($A$2:$A$100,MATCH(B1&"-*",$A$2:$A$1000,0))

Adjust to suit.

 

[brooklyn2007]

C1, copy down:

=INDEX($A$2:$A$100,MATCH(B1&"-*",$A$2:$A$1000,0))

Adjust to suit.

God bless you

 

[Aladin Akyurek]

God bless you

You are welcome.