# How do I use the multiply line results from query or filter to get other values from an unsorted sheet?

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.

``````=SUM(ARRAYFORMULA({QUERY('Customer Orders'!\$A\$2:\$C,