0

I'm trying to find the lowest price for any item 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 array 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?

2
  • I'm not sure right now but certain commands break if a value is missing. So you might be having issues because you got empty cells in the list for Site1 and Site2. – Seth Apr 28 '17 at 8:47
  • No, that's not it. The issue is definitely that the MATCH part of the formula is only picking up one from the array {one, two, four}. I've tested filling in other values and it always returns the value in the one row. – Jamie Bull Apr 28 '17 at 8:49
1

I don't understand the approach of your array formula, but this formula works:
=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)

enter image description here

1
  • 1
    I was just guessing at what might work if I'm honest. Yours works perfectly though – Jamie Bull Apr 28 '17 at 9:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.