Excel macro to perform mass lookups

Discussion in 'Programmer's Corner' started by Yogibear76, Apr 30, 2013.

  1. Yogibear76

    Thread Starter New Member

    Apr 30, 2013
    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.
  2. tshuck

    Well-Known Member

    Oct 18, 2012
    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...
  3. Yogibear76

    Thread Starter New Member

    Apr 30, 2013
    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.
  4. Arm_n_Legs

    AAC Fanatic!

    Mar 7, 2007
    You can use the VLOOKUP function in excel to pick up data from a lookup table.
  5. John P

    AAC Fanatic!

    Oct 14, 2008
  6. vpoko

    Active Member

    Jan 5, 2012
    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.
  7. GopherT

    AAC Fanatic!

    Nov 23, 2012
    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?