INDEX + MATCH Part Text of range values

[hro5e]

I have a range of name values in SHEET 1, COLUMNS A&B:

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Sheet 1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DEL01[/TD]
[TD]CV[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]DEL02[/TD]
[TD]CV[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]DEL03[/TD]
[TD]SS[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]DEL04[/TD]
[TD]CV[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]DLM101[/TD]
[TD]FD[/TD]
[/TR]
</tbody>[/TABLE]











I have a second range of description values in SHEET 2,COLUMN C:

[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]SHEET 2[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]DEL02.START[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]DEL02.STOP[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]DEL02.PUSH[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]DEL02.PULL[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]DLM101.START[/TD]
[/TR]
</tbody>[/TABLE]











How can I in SHEET 2 COLUMNS A&B, INDEX SHEET 1 COLUMNs A&B and INDEX SHEET 2 COLUMN C and MATCH part of the value and insert COLUMN A's values into COLUMN B that *part match* COLUMN C.

Giving the final results:


[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]SHEET 2[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DEL02[/TD]
[TD]CV[/TD]
[TD]DEL02.START[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]DEL02[/TD]
[TD]CV[/TD]
[TD]DEL02.STOP[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]DEL02[/TD]
[TD]CV[/TD]
[TD]DEL02.PUSH[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]DEL02[/TD]
[TD]CV[/TD]
[TD]DEL02.PULL[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]DLM101[/TD]
[TD]FD[/TD]
[TD]DLM101.START[/TD]
[/TR]
</tbody>[/TABLE]











As you can see columns A & B have been automatically filled in because part of column C value matches values in SHEET 1 column A.

Any help would be great.

 

[alansidman]

Results:

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]

SHEET 2​

[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]


[tr][td]

2​

[/td][td]

1​

[/td][td]

=LEFT(D2,FIND(".",D2)-1)​

[/td][td]

=VLOOKUP(Sheet2!B2,Sheet1!$B$2:$C$6,2,FALSE)​

[/td][td]

DEL02.START​

[/td][/tr]


[tr][td]

3​

[/td][td]

2​

[/td][td]

=LEFT(D3,FIND(".",D3)-1)​

[/td][td]

=VLOOKUP(Sheet2!B3,Sheet1!$B$2:$C$6,2,FALSE)​

[/td][td]

DEL02.STOP​

[/td][/tr]


[tr][td]

4​

[/td][td]

3​

[/td][td]

=LEFT(D4,FIND(".",D4)-1)​

[/td][td]

=VLOOKUP(Sheet2!B4,Sheet1!$B$2:$C$6,2,FALSE)​

[/td][td]

DEL02.PUSH​

[/td][/tr]


[tr][td]

5​

[/td][td]

4​

[/td][td]

=LEFT(D5,FIND(".",D5)-1)​

[/td][td]

=VLOOKUP(Sheet2!B5,Sheet1!$B$2:$C$6,2,FALSE)​

[/td][td]

DEL02.PULL​

[/td][/tr]


[tr][td]

6​

[/td][td]

5​

[/td][td]

=LEFT(D6,FIND(".",D6)-1)​

[/td][td]

=VLOOKUP(Sheet2!B6,Sheet1!$B$2:$C$6,2,FALSE)​

[/td][td]

DLM101.START​

[/td][/tr]
[/table]

 

[hro5e]

Thanks for posting,

Although the descriptive data in Column C is not always structured the same.

It can vary for example:

DEL02.START
MISCHECK_NO FOR DEL02
BAGCOUNT:DEL02
DEL02_CC
DEL02-Counting

So I can't use your suggested formula (LEFT of ".")

Any ideas.