 # Question about lookups in Excel array (CSE) formulas

Hello,
I was wondering if anyone could help me with creating an array formula in Excel. I am trying to do a lookup on a value and sum the costs all in one cell. Attached is a workbook that has a simple example of the idea I'm trying to accomplish. The total cell should lookup the price for each food item and multiply it by the amount. It should then sum those values and tell me the total price.

For some reason, the lookup formula isn't working. It looks up the value for price of the first item in the list and multiplies the quantities by that price alone. It should look up an item's price and multiply its quantity by that price then lookup the next price and do the same for that fruit's quantity. I'm not sure why it's not working. Can you do lookups in CSE formulas?

Example.xlsx Last Comment
Professor J

8/22/2022 - Mon
Professor J

Example.xlsx
prophet001

Hello and thanks for the response. I know that I can do this calculation in steps but I am wanting to get it as a single value which is a part of another formula.

Any way to do this?
Professor J

i am not sure if i understand what you really want to achieve. the lookup functions takes care of them and are dynamic. can you please perhaps show me what formula did you try as array CSE that did not work?
prophet001

Here is the actual array formula that I am using:

``````=SUM(IF(NOT(\$K\$12:\$K\$16=0),INDEX(\$B\$34:\$C\$36,MATCH(\$E\$12:\$E\$16,\$B\$34:\$B\$36,0),2)*IF(\$G\$12:\$G\$16="OT",1.5,1)*\$I\$12:\$I\$16,0))
``````

Aside from the conditions, it is supposed to look at the values in the 5 rows of E12:E16 and for each one lookup a cost in the second column of the array B34:C36. It should then multiply the cost that it finds by the quantity in I12:I16. This formula is entered as a CSE formula.

What the formula ends up doing is looking up the value of the first row in E12:16 and using that value for every row multiplication with I12:I16 instead of repeating the lookup.
Professor J

prophet001

Example2.xlsx

OK. What the labor cost cell should do is look up the cost for J or H from the table at the bottom and multiply those by the number of hours. As it was uploaded, the formula is only looking up the first value (H) and is using that for all multiplication. It does not look up each row independently for some reason.
Professor J

still confused. if you could please tell me, supposedly what should the end result of formula be in amount?
prophet001

The labor cost cell should show 1 hour of H and one hour of J which is \$110.00 (\$40.00 + \$70.00). Instead it is showing 2 hours of H which is \$80.00. It is looking up H for the first row in the table and using that value for each subsequent row's calculation instead of looking up J's value when it gets to the second row.
Professor J

this is the formula with CSE
``````=SUM(INDEX(\$C\$14:\$D\$15,N(IF(1,MATCH(\$F\$4:\$F\$5,\$C\$14:\$C\$15,0))),2))
``````
Example2.xlsx
prophet001

That works but a few questions. First, I'm wanting to allow F4:F8 be optional inputs and need the formula to filter based on whether or not the cell has a value. Second, why does this work and the formula that I was using doesn't? I need to understand how to construct these if you're able to help with an explanation.

Thank you.
Professor J

it is late at night here, i can provide full explanation tomorrow.
Rob Henson

@Prof - your formula does not reference the number of hours in column H, should it?

If the cost column was included in the table at the top, or the number of hours for each cost category included at the bottom, it would be a very simple SUMPRODUCT formula.

=SUMPRODUCT(H4:H8,I4:I8)  gives \$190 as per the table at the top.

This looks at each cell in range H4:H8 and multiplies the contents by the equivalent cell in I4:I8 and adds it all up. Effectively doing

=SUM(H4*I4,H5*I5,H6*I6,H7*I7,H8*I8)

Thanks
Rob H
Subodh Tiwari (Neeraj)

You may also try the following UDF (User Defined Function) in order to get the total labor cost without any helper columns or calculations.
``````Function TotalLaborCost(oRng As Range, vRng As Range) As Double
Dim x, y
Dim i As Long, j As Long
Dim Total As Double, n As Double
x = oRng.Value
y = vRng.Value
For i = 1 To UBound(x, 1)
If x(i, 1) <> "" Then
For j = 1 To UBound(y, 1)
If x(i, 1) = y(j, 1) Then
If x(i, 2) = "ST" Then
n = 1
Else
n = 1.5
End If
Total = Total + x(i, 3) * y(j, 2) * n
End If
Next j
End If
Next i
TotalLaborCost = Total
End Function
``````
Then use the above function on the sheet like this....
``````=TotalLaborCost(F4:H8,C14:D17)
``````
For more details, refer to the attached.
Total-Labor-Cost.xlsm
Professor J

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
prophet001