1

I'm trying to take the results of a filter with multiple lines and use that results to acquire info from another sheet. Specifically, I'm trying to acquire Quantities in Customer Order sheet using Purchase ID from Total Cost sheet and the Unit Prices in the Database sheet through the Product Names in Customer Orders sheet. I than multiple the values in this case Quantity * Unit Price = Total Cost. Here is a visual on what I'm trying to do.

Example:

Sheet 'Total Cost'

Purchase ID  Total Cost  Should be
BH2          $48.00      $59.00
KP3          $7.00       $52.00

Get Purchase ID from column A

Sheet 'Customer Orders'

Product Name  Quality   Purchase ID
Black Hats    12        BH2
Brown Shoes   1         BH2
Trimmers      7         BH2
Black Hats    11        KP3
Brown Shoes   2         KP3

Get Quantities that match the Purchase ID from the Customer Orders sheet Get Product Names from Customer Orders using the Purchase ID

Sheet 'Database'

Product Name    Unit Price
Black Hats      $4.00
Pink Hats       $3.00
Blue Hats       $5.00
Purple Hats     $7.00
Brown Shoes     $4.00
Trimmers        $1.00

Use the Product Names from Customer Orders through the use of the Purchase ID to acquire the Unit Prices from the Database sheet

Formulas I have tried:

This one gives the list of names but I can't seem to reuse the array of names to get the prices of all the names in the array from the Database

Result  Formula                             
$4.00   =QUERY(Database!A2:B,"select B where A = '"&QUERY('Customer Orders'!A2:C, 
        "select A where C = '"&$A2&"'",0)&"'")

I can pull the first quantity in the list but I need all the quantities matching A2 which is BH2 in this case. Which should be the quanities 12, 1 and, 7

Result  Formula                             
12      =INDEX('Customer Orders'!$A$2:$B,MATCH(A2,'Customer Orders'!C2:C,0),2)  

I can get the list of names from the Customer Orders sheets but plugging the formula into another formula yeilds the same as the QUERY example.

Result       Formula
Black Hats   =FILTER('Customer Orders'!A2:A,'Customer Orders'!C2:C = A2)
Brown Shoes
Trimmers

Result       Formula
$4.00        =QUERY(Database!A2:B,"select B where A = '"&
             FILTER('Customer Orders'!A2:A,'Customer Orders'!C2:C = A2)&"'")

The end result of my formulas is this but it doesn't use any more than the first entry. The output should be $59.00

Result           Formula
$48.00           =INDEX(Database!$A$2:$C,MATCH(INDEX('Customer Orders'!$A$2:$B,
                  MATCH(A2,'Customer Orders'!C2:C,0),1),Database!$A$2:$A,0),2) * 
                  INDEX('Customer Orders'!$A$2:$B,MATCH(A2,'Customer Orders'!C2:C,0),2)

How I'm currently thinking the steps could go.

Step 1 Get Purchase ID from column A
Step 2 Get Quantities that match the Purchase ID from the Customer Orders sheet
Step 3 Get Product Names from Customer Orders using the Purchase ID
Step 4 Use the Product Names to acquire the Unit Prices from the Database sheet
Step 5 Quantity * Unit Price = Total Cost

Spreadsheets with the examples I'm shown here.

Editable: Link Read only: Link

I hope all of this makes sense. I'll try and answer any questions I can.

0

=SUM(ARRAYFORMULA({QUERY('Customer Orders'!$A$2:$C, 
                         "select B where C ='"&A2&"'", 0)} *
                  {ARRAYFORMULA(VLOOKUP(
                   QUERY('Customer Orders'!$A$2:$C, 
                         "select A where C ='"&A2&"'", 0),
                   Database!$A$2:$B,2,0))}))

4

  • That is exactly what I needed. Thank you so much for your time and your knowledge on this topic <3. – TheDT Jan 11 at 7:33

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.