Is it possible to use an array as MATCH criteria?

**oCoCarbon** · 04-28-2017, 04:12 AM

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.

	A	B	C	D	E
1	Item	Site1	Site2		Valid
2	one		1.5		one
3	two	2			two
4	three		1		four
5	four	7	8		
6	five	1			
7					
8	Best	0	1.5

The formula in B8 is:

{=MIN(INDEX(B2:B6,MATCH($E$2:$E$4,$A$2:$A$6,0)))}

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** · 04-28-2017, 05:41 AM

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** · 04-28-2017, 05:43 AM

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

=MIN(IF(ISNUMBER(MATCH($A$2:$A$6,$E$2:$E$4,0))*ISNUMBER(B$2:B$6),B$2:B$6,""))

Where:

ISNUMBER(MATCH($A$2:$A$6,$E$2:$E$4,0)) checks whether item is included in list of valid items
ISNUMBER(B$2:B$6) checks whether current item has a price or it's empty (otherwise Excel would automatically convert blank to 0)

**oCoCarbon** · 04-28-2017, 05:47 AM

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

**FlameRetired** · 04-28-2017, 01:59 PM

Withdrawn by FR.

Is it possible to use an array as MATCH criteria?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Is it possible to use an array as MATCH criteria?

Re: Is it possible to use an array as MATCH criteria?

Re: Is it possible to use an array as MATCH criteria?

Re: Is it possible to use an array as MATCH criteria?

Re: Is it possible to use an array as MATCH criteria?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Array Match and Return value upon multiple array match criteria

Index Match Over Array with 3 Criteria

Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail

[SOLVED] SUMIFS to match array of Criteria

[SOLVED] Index Match in an Array 2 Criteria

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions