Formula to either pull multiple values with an Hlookup or a substitute formula instead of an Hlookup

New Contributor

Hello everyone,


I am trying to either update my hlookup, or come up with a different formula when pulling values.


My file includes a tab that has all my general ledger accounts in one row and project managers update the cost by day based on invoices credit cards etc. I have a row where I sum the cost through a period of time and I have an hlookup formula (=HLOOKUP(A23,'Daily Updates'!$AG$5:$EG$7,3,0)) that pulls that value to a comparison sheet.


When I receive the files there are times where the project managers add a new column with the same GL account to track it easier, but since there are now duplicates of the same number, the Hlookup only gives me the first column's value with that account number.


My current process is to manually sum all values with that GL account and place it in the front of the first column to make sure the hlookup pulls that value to ensure I have all the cost, but I am wondering if someone can help me come up with a formula or a function that can help this process be less manual.


I am currently using Microsoft 2016.


All help is greatly appreciated!

2 Replies
best response confirmed by mwb014 (New Contributor)


=SUMPRODUCT(('Daily Updates'!$AG$5:$EG$5=A23)*'Daily Updates'!$AG$7:$EG$7)

You can try the SUMPRODUCT formula. 


Thank you so much! That function is exactly what I needed.