Dynamic VLOOKUP on groups of numbers separated by /

barim

Board Regular
Joined
Apr 19, 2006
Messages
171
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.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
Try:
Code:
=VLOOKUP(--LEFT(A2,FIND(" /",A2)-1),MyFileRange,10,0)&" / "&VLOOKUP(--RIGHT(A2,FIND("/ "),A2)-1),MyFileRange,10,0)
 

barim

Board Regular
Joined
Apr 19, 2006
Messages
171
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

Board Regular
Joined
Apr 19, 2006
Messages
171
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
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 19, 2006
Messages
171
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
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,140
Messages
5,599,964
Members
414,352
Latest member
macquarie_jchan58

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top