In sheet Input Variable, I have a cell F3 containing multiple items selected from a drop down (without repetition) and separated with comma.

drop down

Their lookup values are in another sheet Ref Data as shown below:

ref data

I would like to get their sum in cell G3.

=VLOOKUP(F3,'Ref Data'!B:C,2,FALSE)

So far I am getting value for only one item.

For example:

one item

But when I select another item from the drop down, I am getting #N/A value

For example:

For items Delivery, Pilot, value should have been 1500 (1000 + 500)

multiple item

How may I resolve this issue ?


You can use the following formula:


A better explanation than I could come up with for how it works can be found here


If the list contains elements which completely embed other elements such as "All Extras" has "All" in it and "copilot" completely embeds "pilot" (all lowercase for illustration purposes as FIND is case sensitive so "Pilot" is not in "Copilot"), use this additional bracketing so there are no erroneous charges.

=SUMPRODUCT(--(ISNUMBER(FIND(", "&B2:B5&",",", "&(F3)&","))),C2:C5)

Adding the commas to the selection "Delivery, Pilot" makes it ", Delivery, Pilot,". Adding the commas to the array B2:B5 becomes {", Pilot,";", Delivery,"...}. For each of these array items (with their commas), Find returns TRUE when the element is in the selection and FALSE if the complete array element is not found in the selection. If there are commas in the service description, then use a different separator (such as pipe |) in the selection box value and use the same separator to bracket within this formula. The -- double negation turns the FIND resultant boolean array into ones (true=found) and zeros (false=not found). The SUMPRODUCT multiplies this resultant array of ones and zeros by the row value of the corresponding Cost array C2:C5 and all of these products are summed.

Your Answer

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.