Cell value with multiple values to vlookup

[Matheus]

I have one spreadsheet with the a column with a location code and another
column with the number of employees (Colum A2:A200 are the codes and B2:B200
are the number of employees).

On another spreadsheet, some locations are combined into one sigle row. So I
have a cell with the location codes separated by a coma (example: A2 = 100,
200).

Is there a way for me to use a function on B2 similar to a vlookup that will
look both values from A2 and add them up?

 

[~L]

Find the first term in A2:
LEFT(A2,LEN(A2)-FIND(",",A2)-1)
This outputs as text.

Find the second term in A2:
RIGHT(A2,LEN(A2)-FIND(",",A2)-1)
This outputs as text too.

Sum the two (Which you can only do if they're numbers):
=LEFT(A2,LEN(A2)-FIND(",",A2)-1)+RIGHT(A2,LEN(A2)-FIND(",",A2)-1)
The outcome will be a number.

You mention a lookup, which leads me to believe that A2 contains two text
items that you need to look up values for in a table. If that's the case and
A2 actually contains "Cat, Dog" and you need to look up the value for Cat and
Dog in the table, then add those:

=VLOOKUP(LEFT(A2,LEN(A2)-FIND(",",A2)-1),ValueTableLocation,2,False)+VLOOKUP(RIGHT(A2,LEN(A2)-FIND(",",A2)-1),ValueTableLocation,2,False)

Hopefully something in there is useful to you.

 

[Max]

One way
Assuming source data in Sheet1 cols A and B, from row 2 down
In another sheet,
you have the lookup values in A2 down
In B2:
=SUMPRODUCT((ISNUMBER(SEARCH(Sheet1!A$2:A$100,A2)))*(Sheet1!A$2:A$100<>""),Sheet1!B$2:B$100)
Copy down. Tested ok here.
If above helps, click YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik

 

[Shane Devenshire]

Hi,

Here is one possibility:

=SUMPRODUCT(ISNUMBER(FIND(Sheet1!A$2:A$200,A2))*Sheet1!B$2:B$200)

 

[Matheus]

Max,

It worked when I created a sample worksheet using Sheet1 and Sheet2 and
fictitional data. But when I copy the formula to the worksheet I'm using it
brings up a #VALUE! error. Can you help?

Here is some more details:

the worksheet's name I'm working is separate from the worksheet that
contains the number of employees per location code. The worksheet name that
has the data is HCount.xls and the sheet name is HC by Country. Column A has
the codes and column C has the number of employees.

So when I put the formula on my other sheet I get the error. Here is the
function I'm using:

=SUMPRODUCT((ISNUMBER(SEARCH('[HCount.xls]HC by
Country'!$A$11:$A$615,BG8)))*('[HCount.xls]HC by
Country'!$A$11:$A$615<>""),'[HCount.xls]HC by Country'!$C$11:$C$615)

 

[Max]

.. it brings up a #VALUE! error.
Check & clean up the source range col C for any #VALUE! error(s) somewhere.
Apply and use autofilter on col C to quickly locate and clear up all of
these, and it should work fine.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---

 

[Matheus]

Max, sorry for being such a pain... correcting the sorce in col C got rid of
the #VALUE! error. That worked.

But now I want to do the same thing for the next column and it is adding the
numbers correctly.

For col BH where I have the formula finding and adding the values on Col C
on the other spreadsheet I get the correct numbers. But when I copy the
formula over ti BI and find/add the numbers on the col D from the other
spreadsheet I get wrong results. Not all the rows have wrong results, the
majority does and the difference between what the number the firmula
calculates and from what the numbers should be are, in the majority of the
cases, -2 or -10... the formula is below I would highly appretiate any help!!

=SUMPRODUCT((ISNUMBER(SEARCH('[HCount.xls]Nov 2008 HC by
Country'!$A$11:$A$590,BG8)))*('[HCount.xls]Nov 2008 HC by
Country'!$A$11:$A$590<>""),'[HCount.xls]Nov 2008 HC by Country'!$D$11:$D$590)

 

[Max]

.. correcting the sorce in col C got rid of

the #VALUE! error. That worked.

That's good.

Start a new thread for your new query
That the formula works is proven
Data quality is a continuing challenge
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---