Search for minimum price with dynamic range and return price and product ref

You can put this array* formula in H5:

Formula:

=INDEX($B$5:$B$17,MATCH(1,(LEFT($C$5:$C$17,LEN($G5))=$G5)*(MIN(IF(LEFT($C$5:$C$17,LEN($G5))=$G5,$D$5:$D$17))=$D$5:$D$17),0))

and this array* formula in I5:

Formula:

=MIN(IF(LEFT($C$5:$C$17,LEN($G5))=$G5,$D$5:$D$17))

and then copy the formulae down.

* An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>. If you do this correctly, then Excel will wrap curly braces { } around the formula when viewed in the formula bar, but you should not type these yourself. If you need to edit the formula subsequently then you will need to use CSE again. You can copy/paste an array formula in the normal way(s).

Hope this helps.

Pete