0

I'm trying to do a vlookup on a cell with comma separated items in it for the lookup_value, referencing another file that has 1 item per cell. My example below has 6 items in the csv cell, and i want the lookup to return any matching values (summed).

Vlookup Example

New contributor
Eric Fox is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

Then you want SUMIFS inside a SUMPRODUCT:

First we need to parse the lookup values, we can do that with MID and ROW to create the array of lookups:

TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",999)),(ROW($XFD$1:INDEX($XFD:$XFD,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*999+1,999))

This will create an array of the lookup values in A2: {1,2,3,4,5,6}

Then we pass the array to the SUMIFS():

SUMIFS(C:C,B:B,TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",999)),(ROW($XFD$1:INDEX($XFD:$XFD,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*999+1,999)))

Then we use SUMPRODUCT to iterate that array and sum the results of each:

=SUMPRODUCT(SUMIFS(C:C,B:B,TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",999)),(ROW($XFD$1:INDEX($XFD:$XFD,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*999+1,999))))

enter image description here

  • Thank you so much for this, worked perfect! – Eric Fox 2 days ago
  • @EricFox please consider marking as correct by clicking the check mark by the answer. – Scott Craner 2 days ago
  • 1 more thing, if I have dates in column D, and I need to add a date range to the sumifs, where in the equation would that go? For example, I would need it to look for all prices that fall between 7/28/2019 & 8/31/201. I know typically I would just use =sumifs(D2,">="&date(2019,7,28),D2,"<="&date(2019,8,31). – Eric Fox 2 days ago
  • you would add: ,D:D,">="&date(2019,7,28),D:D,"<="&date(2019,8,31)before the last two ) – Scott Craner 2 days ago

Your Answer

Eric Fox is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.