Results 1 to 5 of 5

Is it possible to use an array as MATCH criteria?

Threaded View

  1. #1
    Registered User
    Join Date
    04-30-2009
    Location
    Cáceres, Spain
    MS-Off Ver
    Excel 2010
    Posts
    11

    Is it possible to use an array as MATCH criteria?

    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?
    Last edited by oCoCarbon; 04-28-2017 at 04:45 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Array Match and Return value upon multiple array match criteria
    By VegetaOSX in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-05-2015, 05:50 AM
  2. Index Match Over Array with 3 Criteria
    By celestealexandra in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-31-2014, 06:31 PM
  3. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  4. [SOLVED] SUMIFS to match array of Criteria
    By amazinglazers in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-19-2013, 06:25 AM
  5. [SOLVED] Index Match in an Array 2 Criteria
    By Dendrinos2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-27-2013, 08:24 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1