# Basic Array/Sorting Question

#### bpiermat

##### New Member
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

##### Rules violation
Why not just a simple COUNTIF

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

#### Eric W

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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.