Dynamic VLOOKUP on groups of numbers separated by /

barim

Board Regular
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.

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

JackDanIce

Well-known Member
Try:
Code:
``=VLOOKUP(--LEFT(A2,FIND(" /",A2)-1),MyFileRange,10,0)&" / "&VLOOKUP(--RIGHT(A2,FIND("/ "),A2)-1),MyFileRange,10,0)``

barim

Board Regular

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

Board Regular
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

Well-known Member

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

Board Regular
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

Well-known Member
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

Replies
3
Views
341
Replies
0
Views
186
Replies
1
Views
126
Replies
1
Views
183
Replies
8
Views
173

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,706
Messages
5,833,238
Members
430,198
Latest member
KitaYama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back