Using Vlookup inside Sumproduct

Question

I have a table named "Open" that contains values of DPL_900, DP_950, etc. I want to calculate the sum of multi between quantity and each value of DPL_900, DP_950 which "Vlookup" in Open table. So I typed in the cell K98 that formula:

{=SUMPRODUCT(K93:K97,VLOOKUP(L93:L97,Open,2,0))}.

I thought that formula would work but it does not.

Describe clearly what you are trying to achieve please. What is your desired result considering the sample in the screenshot? — JvdV
Thank for responding. I have an example: Value of DPL_900 is 10. Value of DP_950 is 5. Result is 6*10+8*5=140 — Tuan Anh
That's relevant information you should include in the initial question. It's impossible to have guessed these values plus your desired result from the current status of your question. Please address that, see How to Ask a question with a minimal reproducible example — JvdV

Harun24HR Harun24HR · Accepted Answer · 2019-07-26T09:32:56

I think you do not need any VLookup(). Use following formula. Here Open is table name with header.

=SUMPRODUCT(B2:B6,(Open[Cat]=C2:C6)*Open[Num])

Using Vlookup inside Sumproduct

1 Answers