Look up for mutliple values in one cell

[Salkhatib]

Hello All,

I hope you are all safe and well. I have clients with various codes and revenue amounts. I want to lookup the total revenue of multiple codes separated by comma in one cell. Can you please provide me with a formula to do that. Please note that separating the codes to different cells would take a lot of time and effort.

Thanks
Sara

[MRUTOR]

Pls provide a sample workbook.

[Salkhatib]

Client Job codes Vlookup total NFR
ABC 152378, 152377 ??
XYZ 152294, 147875 ??
EDV 152236, 152236 ??


accounts job code NFR
ABC 152378 15,629.06
ABC 152377 15,273.30
XYZ 152294 15,000.00
XYZ 147875 14,942.25
EDV 152236 14,747.60
EDV 152236 14,728.00

[JeteMc]

Hello Salkhatib and Welcome to Excel Forum.
Here are three proposals.
1. In my opinion the simplest solution is to sum by client, as in: =SUMIFS(Q$3:Q$8,O$3:O$8,B3)
2. If there are no duplicate job codes then the job codes could be parsed using: =IFERROR(VALUE(TRIM(MID(SUBSTITUTE($C9,",",REPT(" ",LEN($C9))),(COLUMNS($A$1:A$1)-1)*LEN($C9)+1,LEN($C9)))),"")
The sums could then be produced using: =SUMPRODUCT((P$3:P$8=E9:G9)*(Q$3:Q$8))
3. If there are duplicates then after parsing the job codes duplicates may be removed using: =IFERROR(IF(MATCH(0,INDEX(COUNTIF($H15:H15,$E15:$G15),,),)>COUNT($E15:$G15),"",INDEX($E15:$G15,MATCH(0,INDEX(COUNTIF($H15:H15,$E15:$G15),,),))),"")
The sums are then produced using a SUMPRODUCT based formula as in proposal #2.
Let us know if you have any questions.