• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

lookup one value and return multiple corresponding values

I

iceplant

New Member
Hi, first thank you for any help :)
Here is the question. I have one data tab which constantly changes. (adding more products and corresponding volume/prices) For the lookup tab, I want to look up one value and return multiple values.
I have figured out an index array formula but it just calculates so slowly. My live file is about 8MB now. When I apply my formula, it takes forever to save. Just want to see if there are better formulas to solve this problem.
Thanks
 

Attachments

Hi @iceplant

On your data sheet put some helper columns which multiply Price * Quantity. Now you only need to sum two criteria by one column. Takes some pressure off Excel.

=SUMPRODUCT((Data!$B$3:$B$35=$B$4)*(Data!$C$3:$C$35=$B7)*(Data!L$3:L$35))

And a sumif variant

=SUMIFS(Data!L$3:L$35,Data!$B$3:$B$35,$B$4,Data!$C$3:$C$35,$B7)

This will make your life easier. File attached to show workings.

Take care
Smallman
 

Attachments

You must log in or register to reply here.
Back
Top