Is it possible to use an array as MATCH criteria?

[oCoCarbon]

I'm trying to find the lowest price for any product in a group of substitutable products. My thought was to use an array formula but it doesn't seem to work.

Please Login or Register  to view this content.

The formula in B8 is:

Please Login or Register  to view this content.

It's working for Site 2, but only because the MATCH part of the formula only picks up the first item from the list of valid items, rather than the whole array as I had expected.

Is there another way to approach this?

[lecxe]

Hi

Try, for ex., this array formula:

=MIN(IF(COUNTIF($E$2:$E$4,$A$2:$A$6),IF(ISNUMBER(B2:B6),B2:B6)))

[oCoCarbon]

I cross-posted this at SuperUser here and got an answer that works. Here it is for future searchers:

Please Login or Register  to view this content.

Where:

Please Login or Register  to view this content.

[oCoCarbon]

Thanks, @lecxe. I now have a couple of options to try out and see which is faster.

[FlameRetired]

Withdrawn by FR.