• 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


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

Count and Lookup


Active Member
Since no one here is a mind reader, it is necessary for you to explain in sufficient detail the calculation you want. Explain in simple terms with enough detail so that it can be replicated in Excel. What you have provided is of no help at all.

salim hasan

New Member
Try this array formula (Ctrl+Shift+Enter)
=IFERROR(IF((MATCH(TRUE,INDEX($E$1:$H$10,MATCH($A$15,$B$1:$B$10,0),)=$B$15,0)-1)=0,1,MATCH(TRUE,INDEX($E$1:$H$10,MATCH($A$15,$B$1:$B$10,0),)=$B$15,0)-1),"Not Found")



Thanks Salim Hasan its works for only the data that doesnt have duplicate but how the results for duiplicates? Thanks
Last edited:

Peter Bartholomew

Well-Known Member
Sorry, what you want is totally opaque. I can count the matches, I can even determine their location, but I have no idea what it is you want. For better or worse, the count is given by the array formula
{= SUM( (YearList=Yr) * (ValueArray=value) )}