Matching columns with similar strings

Question

I am trying to match the columns based off the first 8 characters and the last 2 characters so i would then be able to run a vlookup on it:

The issue is the middle of the cell is always different in the first column is all 10 in the middle, the second 25 and so on. I tried collecting the first 8 and last two characters by a left and right and then tried an approx vlookup but it wasn't working for me just showing an error for me.

My attempt

=LEFT(C2,FIND("/",C2)+3) & "    " & RIGHT(C2,FIND("/",C2)-3)

Gives me this:

20.5/010    ES

Trying to run off Col B in the photo

Desired result:

Here is the Error I am receiving:

Answer 1

Assuming you have three different type of data e.g.; 20.5/010/010/, 20.5/010/025/ & 20.5/010/022/ and you will delete your column B (which is empty in your input data) you will have column A, B and C populated (Makes sense?)

Your output data clearly shows you have a list of 010 values in column 10 and you want to know if there is a match for the other two values with the same country code. But I'll make that column D in this example. So:

• Have column D populated like your output column 10.

• Add this formula to column E and drag down:

  =IFERROR(INDEX(B:B;MATCH("20.5/010/025/"&RIGHT(D1;2);B:B;0);1);"")
  

• Add this formula to column F and drag down:

  =IFERROR(INDEX(C:C;MATCH("20.5/010/022/"&RIGHT(D1;2);C:C;0);1);"")
  

My output: