MIN/MAX Value of a Range upon Combo Box Input

[artikyulashun]

Ciao All ~

Attempting to return the minimum value of a Range based on input from a Combo Box.

Combo Box returns value in cell BM6 : 1,2,3,4

Set this table up to use Index/Match formula on :

BR BS BT BU
1 S13:S49 W13:W49 AA13:AA49
2 T13:T49 X13:X49 AB13:AB49
3 U13:U49 Y13:Y49 AC13:AC49
4 V13:V49 Z13:Z49 AD13:AD49




The Index/Match works correctly, but when I place MIN() or MAX() around the formula it returns 0.

=MIN(INDEX($BS$4:$BS$7,MATCH($BM6,$BR$4:$BR$7,0)))

Is there another method/approach I could be using?

Cheers.

 

[DanteAmor]

In your example, the result of Index is: "S13:S49", do you want the formula Min to obtain the minimum value of the range "S13:S49"?

If so:



=MIN(INDIRECT(INDEX($BS$4:$BS$7,MATCH($BM6,$BR$4:$BR$7,0))))



=MAX(INDIRECT(INDEX($BS$4:$BS$7,MATCH($BM6,$BR$4:$BR$7,0))))

 

[artikyulashun]

That is what I was looking for. Unfamiliar with the INDIRECT function, but will address the gap.

Thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.