Match Value With Extra Characters

[kumara_faith]

Hi,

I need to match the batch number in column C with the batch number in column B.

If it matches, then it should populate the value in column D.

The problem is the batch number in column B is actually the same as column C but it has last 3 extra characters.

Is there any way I can create a formula that would read the number in column C and match it with column B even though the last 3 characters are different?

Excel Workbook
	B	C	D
3	Batch Number 1	Batch Number 2	Match
4	VB123456-33	VB123456	#N/A
5	VB123466/22	VB123466	#N/A
Sheet1

Appreciate help. Thanks in advance.

 

[Jonmo1]

Is the part you're looking up always the first 8 digits of the code?

Perhaps..

=VLOOKUP(LEFT(B4,8),C:C,1,0)

Hope this helps...

 

[Andrew Poulsom]

Try:

=VLOOKUP(C4&"*",B:B,1,0)

 

[kumara_faith]

Jonmo and Andrew,

My apologies.

I left out some important details.

If column C matches column B, then it should populate value in column D.

Excel Workbook
	B	C	D	E
3	Batch Number 1	Batch Number 2	Date	Match
4	VB123456-33	VB123456	6/16/2008
5	VB123466/22	VB123466	6/15/2008
Sheet1

I tried incorporating both formula's but it did not return the correct value.

Appreciate your assistance. Thank you for your time and patience.

 

[Jonmo1]

You almost had it..

Vlookup(C4&"*",B:D,3,0)

OR

INDEX(D:D,MATCH(C4&"*",B:B,0))

 

[kumara_faith]

Jonmo1,

Thank you for your solution.

What does the &"*" mean in the formula ?

Thank you again for your time and patience.

 

[Jonmo1]

It's a wildcard, so it says match C4ANDANYTHING in Column B