Match or Lookup and using left

[tpollock]

So I have to take data from one sheet and some how match or lookup customer number and return it to another sheet. I have attached a example so you guys can see what I am talking about. I need to come up with a formula to take customer number from sheet two and match the number to customer # on Sheet 1. Haven't messed around with it just figure I would check here first. Also I need to use a Left command in the formula to only search a certain number of characters. Any help would be great thanksExample.xlsx

[sweep]

Hi,

In B2 on sheet1

=INDEX('Sheet 2'!$B$2:$B$27,MATCH(TRIM(Sheet1!C2),'Sheet 2'!$C$2:$C$27,0))

copied down.

Note I have added the trim function to accommodate trailing spaces.

[tpollock]

what value do I need to change if I only want to do the match for left 5 characters so on. thanks formula works great.

[AlKey]

This one will get you more results

=IFERROR(VLOOKUP(LEFT(C2,12)&"*",CHOOSE({1,2},('Sheet 2'!$C$2:$C$27),'Sheet 2'!$B$2:$B$27),2,0),"Not Found")

Or you can modify formula suggested by sweep:

=IFERROR(INDEX('Sheet 2'!$B$2:$B$27,MATCH(LEFT(C2,12)&"*",'Sheet 2'!$C$2:$C$27,0)),"Not Found")

[tpollock]

Thanks very much is there something I need to change in the formula if I am referencing numbers??