I have a sheet below in which I'm trying to find out the difference between ordered products and received products. let's assume we ordered a product name ABC 100 units but only got 80. so when I enter ABC in the balance column it must show the difference(20). maybe we order this ABC in January and received in February, so it might not be in the same row.
https://docs.google.com/spreadsheets/d/1d3KTsn-2VPOfcXKulb_HGuk_iXT_hzJ6vxYA61wc-XI/edit#gid=0
This is easily done with VLOOKUPs.
=ArrayFormula(IFNA(
VLOOKUP(BalanceNames,Ordered,2,0)-
VLOOKUP(BalanceNames,Received,2,0)
))
If you have multiple items in your list and want to sum them, this is similar, but a bit more complex:
=ArrayFormula(IFNA(
HLOOKUP(BalanceNames,QUERY(Ordered,"select sum(D) pivot C"),2,0)-
HLOOKUP(BalanceNames,QUERY(Received,"select sum(G) pivot F"),2,0)
))
We can use a Query pivot to sum the data. This gives a horizontal range, so it would be best to use HLOOKUP.