# How to look up the lowest price for any of a list of substitutes? [closed]

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?

• 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

`=MIN(IF(ISNUMBER(MATCH(\$A\$2:\$A\$6,\$E\$2:\$E\$4,0))*ISNUMBER(B\$2:B\$6),B\$2:B\$6,""))`
• `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`)