Dynamic VLOOKUP on groups of numbers separated by /

[barim]

Here is my problem:
I have groups of numbers separated by “/” and I am trying to do VLOOKUP which I can perform with this formula:

=VLOOKUP(--LEFT(A2,6),MyFileRange,10,FALSE)& " / "& VLOOKUP(--(RIGHT(A2,6)),MyFileRange,10,FALSE)

This only works when I have 2 groups of numbers 6 characters each. When I have let’s say 123 / 4856 it doesn’t work until I change the number of characters in LEFT and RIGHT formula. I can have various combinations of these numbers. What I know is that they cannot exceed length of 6 characters. They can be 5 groups of numbers, sometimes 4 and so on. How can I make this more dynamic? I tried to include function LEN which will count number of characters and pass it to VLOOKUP, but I cannot make it work. Any help would be greatly appreciated.

 

[JackDanIce]

Try:

=VLOOKUP(--LEFT(A2,FIND(" /",A2)-1),MyFileRange,10,0)&" / "&VLOOKUP(--RIGHT(A2,FIND("/ "),A2)-1),MyFileRange,10,0)

 

[barim]

Thank you JackDanIce so my for your reply.

This formula works when I enter sets of 3 characters. 123 / 456, but as soon as I replace any of these with 4 characters like 123 / 4567 it returns #VALUE! error message.

 

[barim]

Update: This formula works on same number of characters from both sides. If I have 123/456 it works. If I have 1234/4567 it works, but if I have 123/4567 it doesn't work. Does anybody know what is the problem?

 

[JackDanIce]

Did you include the space before or after the / in the find part? In my formula, the LEFT has a FIND("_/",A2) (where _ represents a space) and RIGHT has a FIND("/_",A2) then a -1 to remove any spaces.

 

[barim]

I would rather not to have any space in between. I adjusted your formula by eliminating spaces and 123/456 works for me, or 1234/4567 also works. I did not touch -1 at all. But why is it not working if I have 123/4567 or vise versa? It doesn't allow input of different number of characters. Thank you so much for these great answers.

 

[JackDanIce]

Your original post suggested a space before and after the forward-slash so I suggested a formula accounting for this. Anyway, glad it works now and you're welcome