vlookup of multiple references in same cell

**darthshani** · 08-10-2009, 03:48 PM

Hi All

Hope someone out therecan help put me on the right track to solving this problem.

I have multiple cells which all have various amounts of data in them, the core part of the data is always in the same format but the string length of the data can vary from cell to cell. I need to be able to use each segment as a reference for a Vlookup which will ultmately sum up all the results of the individual lookups.

Cell format is

A1 DP 001DP 002 DP 003DP 004
A2 DP 125DP 235 DP 555DP 652DP 555DP 012
ETC

The data I need to extract and perform lookup with is DP xxx, so I want to look at a section of the cell which is always 6 characters long .

The cells are in a column and each cell is of a varying legth but always in multiples of 6. I am able tp extract the information by just using a simple left/right character formula but this is a very manual process and I have a grid of 90*90 cells that i need to work on.

Hope someone can help

M

**Domenic** · 08-10-2009, 04:33 PM

Somewhat unclear... VLOOKUP accepts wildcard characters. So, for example, your lookup value can be as follows...

"*DP 002*"

or

"*"&A2&"*"

...where A2 contains DP 002.

**JBeaucaire** · 08-10-2009, 04:35 PM

You don't go far enough in explaining your need. You already know how to isolate 6 characters in a cell, so that's not your problem.

I am guessing it is your final results that are eluding you. Can just post up you 90x90 grid of data and a manual mockup of some (all?) of the results you want to get?

If we can see you starting line and your goal line, the stuff in the middle should get much easier to suggest.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**NBVC** · 08-10-2009, 04:56 PM

Assuming you have this: "DP 125DP 235 DP 555DP 652DP 555DP 012" in A1,

Then create helper column to determine number of DP items:

=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,"DP","")))/2)

copied down..

then assuming your Lookup table is in H1 to I5, make sure column H is sorted in Ascending order... then you can apply this formula to sum corresponding values:

=SUMPRODUCT(--LOOKUP(MID(A1,FIND("^^",SUBSTITUTE(A1,"DP","^^",ROW(INDIRECT("1:"&$B1)))),6),$H$1:$H$5,($I$1:$I$5)))

copied down. Where B1 contains the Helper column result, correspondingly

Adjust ranges to suit.

**darthshani** · 08-10-2009, 05:08 PM

Sorry for lack of info, I have atached an example of what I am trying to do.

Range name "data" is the range hich I want to do individual Vlookups on.

Range name "route_link" contains the cells which I want to examine, the model I am working on will calculate distances between 2 different sites and the refernce DP xxx is actually a specific route on a network map. The input in this range is simply a concatenation of the links that need to be traversed to get from point 1 to point 2.

Range name "route_km" is where I wish to put all my calculated distances.

I have cut the number of colums down to 5 but in the Workbook I am working on there will actually be a grid which is 90*90.

so for the first cell I would have end up with Vlookups that sum up to 10384.

Hope this is sufficient.

M

**Domenic** · 08-10-2009, 05:50 PM

Try...

G4, copied down:

=SUMPRODUCT(SUMIF($B$4:$B$43,MID($F4,ROW(INDIRECT("1:"&(LEN($F4)-LEN(SUBSTITUTE($F4,"DP","")))/2))*6-6+1,6),$C$4:$C$43))

Note, however, the following should be more efficient...

First define the following...

1) Select G4

2) Insert > Name > Define

Name: Array

Refers to:

=EVALUATE("{"&SUBSTITUTE(SUBSTITUTE($F4,"DP",""",""DP")&"""",""",","",1)&"}")

3) Click Ok

Then try...

G4, copied down:

=SUMPRODUCT(SUMIF($B$4:$B$43,Array,$C$4:$C$43))