Multiple vlookup "lookup values" in a single cell?

**catccc** · 02-10-2011, 11: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, 11: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, 11: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, 11: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:

Please Login or Register  to view this content.

**NBVC** · 02-10-2011, 11: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, 11: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, 12:16 PM

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..

Please Login or Register  to view this content.

If you do have duplicate search values...

Please Login or Register  to view this content.

I attached the last option sample...

**balbinder** · 12-14-2011, 05: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

**skirl** · 10-19-2015, 05:10 PM

Originally Posted by NBVC

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..

Please Login or Register  to view this content.

If you do have duplicate search values...

Please Login or Register  to view this content.

I attached the last option sample...

Hi,

I know this is an old thread, but I just found it, and it almost helps me perfectly. However, my lookup values are numbers. With this attached sample numbers do not work. Does anybody know if there's another way?

Thanks