Solved

Question about lookups in Excel array (CSE) formulas

Posted on 2016-08-30
16
51 Views
Last Modified: 2016-08-31
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?

 Thank you for any help you can give.

Example.xlsx
0
Comment
Question by:prophet001
16 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41776821
please see attached.
Example.xlsx
0
 

Author Comment

by:prophet001
ID: 41776833
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?
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41776845
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?
0
 

Author Comment

by:prophet001
ID: 41776863
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))

Open in new window


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.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41776874
i looked at your formula. what you have uploaded and your formula ranges are not consistent. can you perhaps upload the worksheet where you used your formula.
0
 

Author Comment

by:prophet001
ID: 41776897
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.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41776913
still confused. if you could please tell me, supposedly what should the end result of formula be in amount?
0
 

Author Comment

by:prophet001
ID: 41776923
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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41776969
please see attached.

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))

Open in new window

Example2.xlsx
0
 

Author Comment

by:prophet001
ID: 41777015
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.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41777030
it is late at night here, i can provide full explanation tomorrow.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41777682
@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.

Based on your existing layout:

=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
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41777794
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

Open in new window

Then use the above function on the sheet like this....
=TotalLaborCost(F4:H8,C14:D17)

Open in new window

For more details, refer to the attached.
Total-Labor-Cost.xlsm
0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 41777832
@prophet001

here is the explanation to the formula.

the formula you created, had a culpirt only on section INDEX($C$14:$D$16,MATCH($F$4:$F$8,$C$14:$C$16,0),2)

if you replace this with my formula INDEX($C$14:$D$15,N(IF(1,MATCH($F$4:$F$5,$C$14:$C$15,0))),2)

your formula will work too.

please see attached.
 
the initial solution i provided to you was very basic ignoring the hour column and etc, with the attached one is basically exactly the same formula as you generated, except that i fixed the part of INDEX and MATCH

now relating the option part, i can see that in the existing formula for F4:F8 to be optional.
if you dont want that condition, then simply use =SUM(IF(NOT($I$4:$I$8=0),INDEX($C$14:$D$15,,2)*IF($G$4:$G$8="OT",1.5,1)*$H$4:$H$8,0))
EE.xlsx
0
 

Author Closing Comment

by:prophet001
ID: 41778015
Thank you for your help.

I don't quite understand though why you have to use the N(IF(1 which is an always true to get the MATCH to properly work over the range. Can you explain please?
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41778033
you are welcome. i am glad i was able to help.


Anytime you want to return an array of multiple rows or column into the row or column argument of Index function, then you need to do a de-referencing which is required and N() function is used to do that. it is explained in great detail here
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Office.Com /myAccount 9 41
increment numbers by 10 11 31
Pattern repeat for units digit of a base number 9 41
Add a range in an Excel graph 5 36
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now