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

Correction in Array formula

navic

Member
To remove a result if a cell is empty, the formula needs to be nested in the IF function, with one condition.
Code:
=IF(D4="","",IF(COUNTIFS(DATA2!$E:$E,$B4,DATA2!$H:$H,$C4,DATA2!$B:$B,$D4)<1,"",INDEX(DATA2!C:C,SMALL(IF((DATA2!$E$2:$E$340=$B4)+(DATA2!$H$2:$H$340=$C4)+(DATA2!$B$2:$B$340=$D4)=3,ROW(DATA2!$B$2:$B$340)),1))))
I'm sorry, but I have no more time for you. I'm going on my vacation. :)
 

jack999

Member
To remove a result if a cell is empty, the formula needs to be nested in the IF function, with one condition.
Code:
=IF(D4="","",IF(COUNTIFS(DATA2!$E:$E,$B4,DATA2!$H:$H,$C4,DATA2!$B:$B,$D4)<1,"",INDEX(DATA2!C:C,SMALL(IF((DATA2!$E$2:$E$340=$B4)+(DATA2!$H$2:$H$340=$C4)+(DATA2!$B$2:$B$340=$D4)=3,ROW(DATA2!$B$2:$B$340)),1))))
I'm sorry, but I have no more time for you. I'm going on my vacation. :)
Thank you for your help and time you have spend for me. May God bless you. Happy Vacation.
 
Top