Multiple vlookup "lookup values" in a single cell?

**catccc** · 02-10-2011, 10:18 AM

Is there a way to put multiple vlookup lookup values in one cell and have the result of multiple vlookups appear in one cell? For instance, I have this table:

Code Amount
A 5
B 6
C 8
D 9

I have a cell that contains "A, B, C"

I want the vlookup to reference the cell that says "A, B, C" and return the sum of the values for A, B, & C, in this case, 19.

I previously did multiple vlookups in one cell with a + operator between them, and had A, B, and C each in a separate cell.

Is there any way to put multiple values in one cell and have the vlookup sum them automatically? They don't have to be comma separated, if that makes a difference. But they will be varying groups of text with a varying number of characters.

**zbor** · 02-10-2011, 10:21 AM

You can use =SUMIF(A:A, "A", B:B)+SUMIF(A:A, "B", B:B)+SUMIF(A:A, "C", B:B)

**NBVC** · 02-10-2011, 10:22 AM

Assuming your table is in A1:B5 and your concatenated lookup string is in F1, then try:

=SUMPRODUCT(--(ISNUMBER(SEARCH($A$1:$A$4,F1))),$B$1:$B$4)

Note: This will not accomodate duplicate characters in F1.... if you need that look into Colin's suggestion below

**Colin Legg** · 02-10-2011, 10:23 AM

Yes, it's possible. It would be much easier (formula-wise and for maintenace) if you were prepared to separate out the A B C into separate cells though?

Suppose your code/amount table is A1:B5 and F2:F4 contains the letters A B and C. To get 19 you could then use this formula:

=SUMPRODUCT(SUMIF(A2:A5,F2:F4,B2:B5))

**NBVC** · 02-10-2011, 10:42 AM

or another way, again if F1 contains the comma-delimited string:

=SUMPRODUCT(SUMIF($A$1:$A$4,MID(SUBSTITUTE(F1,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(F1,",","")))) ,1),$B$1:$B$4))

**catccc** · 02-10-2011, 10:53 AM

Thanks all for the quick replies!

zbor- this works but is inefficient because my data is not as simple as the example. The values in the cell will change and I don't want to retype the lookup values for each instance that I want to use the formula.

Colin- thanks, I really wanted to keep all the criteria to one cell for the lookup by row... That said

NVBC- this works well. Is there any way to accomodate additional columns of data? Let's say the values in column B have the header "Department 1" and there are more columns with different departments. Can this formula be modified so you expand the last range and specify a single header to sum? I hope my question makes sense.

Thanks everyone!

**NBVC** · 02-10-2011, 11:16 AM

Yes you can.

So assuming you now have row 1 headers, and so your data moves down 1 row.

and assuming now the data covers A2:D5 with headers in B1:D1, and your search header value is in F1 and the search string in F2, then try:

If you don't have duplicated search values..

=SUMPRODUCT(--(ISNUMBER(SEARCH($A$1:$A$4,F1))),INDEX($B$2:$D$5,0,MATCH(F1,B1:D1,0)))

If you do have duplicate search values...

=SUMPRODUCT(SUMIF($A$2:$A$5,MID(SUBSTITUTE(F2,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(F2,",","")))),1),INDEX($B$2:$D$5,0,MATCH(F1,B1:D1,0))))

I attached the last option sample...

**balbinder** · 12-14-2011, 04:49 AM

Originally Posted by NBVC

or another way, again if F1 contains the comma-delimited string:

=SUMPRODUCT(SUMIF($A$1:$A$4,MID(SUBSTITUTE(F1,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(F1,",","")))) ,1),$B$1:$B$4))

highly grateful for giving this formula