Excel macro to perform mass lookups

Thread Starter

Yogibear76

Joined Apr 30, 2013
3
Hi, I need a macro to perform mass lookups in excel. The data to be looked up is in column A, while the reference tables are in columns D-E.

Problem is that there are multiple lookup values within one cell itself (refer to image), where the values are separated by "/".

Is there a way to do this in excel as the dataset is very large? Thanks.
 

Attachments

tshuck

Joined Oct 18, 2012
3,534
Yes, it is possible, but tedious to do in a formula, VBA could probably do it with less work, depending on your skill level.

Looking up "excel string tokenization" came with a number of examples of people doing just this...
 

Thread Starter

Yogibear76

Joined Apr 30, 2013
3
tshuck, thanks for the tip....will try it out and see.

Am not very proficient in macro programming, so it's going to be a real challenge to do this.
 

vpoko

Joined Jan 5, 2012
267
You can use the VLOOKUP function in excel to pick up data from a lookup table.
Can't do it with VLOOKUP because what he's looking for may be in the start of a cell, the middle of a cell, etc. The way I'd do it (in a VBA macro) would be to loop over all the rows and do a find in each cell of the first column for whatever you're looking for. If you find it in a cell, count how many slashes exist between the beginning of the cell and the first character of the found string, then extract the corresponding item (based on counting slashes) from the second column.
 

GopherT

Joined Nov 23, 2012
8,009
I think the OP needs to give more detailed information on what the return value looks like.

Do any city codes appear more than one time in column A (in more than one row)?
Will searcher always use the 3-letter codes or will they enter city names too?

What do you want as the return value?
- just a city name?
- the route using 3-letter codes as in A?
- routes with all routes connecting through that city?
- other? (explain)

How many rows will there be?

Will new rows be added?

Will the return values be on the same sheet in Excel or will it be on a different worksheet?
 
Top