# What formula?

#### brooklyn2007

##### Board Regular
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.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.

C1, copy down:

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

C1, copy down:

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

God bless you

Replies
5
Views
118
Replies
3
Views
223
Replies
5
Views
152
Replies
4
Views
218
Replies
6
Views
164

1,207,285
Messages
6,077,529
Members
446,288
Latest member
lihong3210

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back