Basic Array/Sorting Question

bpiermat

New Member
Joined
Aug 8, 2019
Messages
2
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!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,159
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
Joined
Aug 8, 2019
Messages
2
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
Joined
Aug 18, 2015
Messages
9,159
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.
 

Forum statistics

Threads
1,085,498
Messages
5,384,045
Members
401,875
Latest member
Vegarlo

Some videos you may like

This Week's Hot Topics

Top