Basic Array/Sorting Question

[bpiermat]

I try to keep my formulas as simple as possible. Here is my current formula for finding a match in two columns:

{=SUM(IF(F$24:F$43=F44,$D$24:$D$43))}

The issue with this is that it sums instead of just reporting the value. Which is a problem if the results are not numerical.

I tried this:

{(F$24:F$43=F44)*$D$24:$D$43)}

But this returns an array of zeros and my match....so it is close to what I want. But I need a VBA function or other simple method to take this large array and just return the single none zero number

Thanks!

 

[theBardd]

Why not just a simple COUNTIF

=COUNTIF(F$24:F$43,F44)

 

[Eric W]

Welcome to the Board.

If there's only one match, how about:

=IFERROR(INDEX($D$24:$D$43,MATCH(F44,F$24:F$43,0)),"No match")

 

[bpiermat]

Welcome to the Board.

If there's only one match, how about:

=IFERROR(INDEX($D$24:$D$43,MATCH(F44,F$24:F$43,0)),"No match")

Eric,

Thanks for this solution, yes, this will work. I was trying to write this function in a way that does not involve the overhead of a lookup function.

 

[Eric W]

I'd have to do some timings, but I suspect a lookup function would be faster than an array formula. Come back and let us know if your sheet becomes too slow and perhaps we can figure something else out.