• 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.

Look Up with multiple conditions

Hello Dear friends,


I have found this site wonderful and am learning a lot from the expert excel users in this forum. TOns of thanks


I have a attached an excel spreadhseet, where in I need to create a lookup formula with multiple conditions. The current Sum product formula I 'm using now is cumbersome and it is talking a lot of time to update the values. As time goes, I will need to add informations for the rest of the year in the source data. Once I paste the data, it takes a lot of time for me to do anything on the file.


Kindly help. Link for the file below.


http://speedy.sh/QAys5/Look-Up-with-mutiple-conditions.xls


Download at SpeedyShare


Thanks a lot.

Excel Dumbo
 
Hi, Ecel Dumbo!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Look%20Up%20with%20mutiple%20conditions%20%28for%20Ecel%20Dumbo%20at%20chandoo.org%29.xlsx


Your formula is not so cumbersome, if you create several ranges for columns in 'LookUP Data' worksheet, and if you make them dynamic (check the definition with OFFSET function) speed doesn't seem to be an obstacle.


I changed too the first column of 2nd. sheet from text "6, 2012" to date format 30/06/2012. Accordingly, in 1st. sheet, I changed the E1:H1 cell range from number 2012 to date 30/06/2012, 31/07/2012, etc. Cell range E2:H2 is the same as E1:H1, exception made that E1:H1 has a custom date format yyyy (to display only the year) and E2:H2 has a custom date format mmm (to display the three letters of the month).


Then I could replace the long part of the formula by a direct equal comparison.


The formula is then reduced to this:

=SUMAPRODUCTO(($A4=DTTerrSurg)*((((($C4=DTBrandText)+($C4=DTProdSize))*($C4<>""))+(($D4=DTBrandText)+($D4=DTProdSize))*($D4<>""))>0)*(E$1=DTPeriod)*DTNetInvSls) -----> in english: =SUMPRODUCT(($A4=DTTerrSurg)*((((($C4=DTBrandText)+($C4=DTProdSize))*($C4<>""))+(($D4=DTBrandText)+($D4=DTProdSize))*($D4<>""))>0)*(E$1=DTPeriod)*DTNetInvSls)


I didn't check the correctness of it, just worked on it.


Just advise if any issue.


Regards!
 
Hi, Ecel Dumbo!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
Back
Top