Quickest way to find the row with 2 values in different columns of M (i.e., lookup2(z1, z2, A, B) )

[Andy_C]

I am looking for the most efficient/quickest way to find the row containing two values in different columns of M. The function I am envisioning would be an alteration of 'lookup': lookup2(z1, z2, A, B).

I have tried a few different solutions but was hoping for quicker performance. Using the masking method provided my  @Werner_E  and the efficiency of vectorizing demonstrated by @PEAH , I came up with a decent solution but was only able to cut the processing cost by ~30% when compared to Werner's FilterMlt function he helped me with not too long ago (solid function @Werner_E !). I am planning to use this lookup2 function a couple zillions times in processing data so I was hoping to cut processing time by much more!

I also compared the time to run the built-in lookup function as a baseline which averaged ~0.15s @ N=10^5. I figure, looking up two values shouldn't be much more than one, max 2x 0.15 = 0.3s, but I am not able to achieve that.

MCP worksheet with parts of @Werner_E  and @PEAH's work attached. My comparisons on pgs. 3 and 4.

MCP 8.0, Windows 10 Pro

[Werner_E · Accepted solution]

Here is a shorter and slightly faster variant.

However, it should be noted that the timing may be entirely different when significantly larger matrices and vectors are involved.

[Andy_C]

Thank you @Werner_E !! I was so close, haha. So simple: use AND.

So far, with my big data sets I've seen time savings of around 20-25% which is good! In the test sheet, I saw improvements from 28% (vs lookup2.test) to 65% (vs FilterMlt)!! lookup2 is still slower than lookup, but I think we've maxed out this custom functions ability.

Thanks again!

[Werner_E]

You may try to turn the function into a one-liner by omitting the local variable "mask" and putting the vectorized expression directly in the lookup function. Maybe you can speedup the calculation by a few % that way - not sure, though.

[Andy_C]

Yeah, good point. In tests, it looks like ~2.5%. Hey, I'll take it!