Use google query as an array formula to sum values in a column based on information in another column
I have 2 tabs needing worked with. The first tab, market_pull has a function that pulls information from EVE Online's ESI and sorts the information into several columns:
duration | is_buy_order | issued | location_id | min_volume | order_id | price | range | type_id | volume_remain | volume_total
The second tab, bulk_market_data sorts the information into several columns:
Citadel ID | Item Id | Item Name | Volume Ea | Qty Available | Lowest Price | Total Volume | Jita Sell | ISK Difference | % Difference
I need help with the bulk_market_data tab. I need to use the Item Id column from bulk_market_data as a criteria to compare to market_pull the column type_id pull the MIN value from the corresponding row in the price column.
I need to do essentially the same thing except I need to use the Item Id column from bulk_market_data as a criteria to compare to market_pull the column type_id pull the total SUM value from the all the corresponding rows in the volume_remain column.
I'm using array formulas because in the bulk_market_data tab there is about 10,000 rows and when I had a formula in every row for every column the sheet slowed down drastically. Thank you for your time and HERE is a sample spreadsheet with the concept.
use in F4:
=ARRAYFORMULA(IFNA(VLOOKUP(C4:C, QUERY({market_pull!C4:C, market_pull!J4:K},
"select Col2,sum(Col3) where Col2 is not null and Col1 = FALSE group by Col2"), 2, 0)))
use in G4:
=ARRAYFORMULA(IF(C4:C="",,IFNA(VLOOKUP(C4:C, SORT(QUERY({market_pull!C4:J},
"select Col8,Col6 where Col1 = FALSE"), 1, 1, 2, 1), 2, 0), 0)))
use in H4:
=ARRAYFORMULA(IF(C4:C="",,ROUNDUP(IF(
E4:E1004*F4:F1004=0,,E4:E1004*F4:F1004), 1)))
