How to make excel auto recognise the min value and substitue it into new table

[azimuiz]

hi guys, if you see my workbook, I had problem to determine min value at column D from table 1 and substitute all its row value into Y1 at table 2 (X1=Y1). How to make excel auto recognise the min value and substitue it into new table even I change all the number..will someone help me..Thank you

Book1.xlsx

[Mayda89]

Use the min() function?

[FDibbins]

Hi

This formula, copied across will give you the results you show in C11:F11.

=INDEX($B$3:$F$6,MATCH(MIN($F$4:$F$6),$F$3:$F$6,0),MATCH(I$3,$B$3:$F$3,0))

[azimuiz]

your answer is almost there ..But how if the min value exist at X2? then i want to substitute it into Y2..i mean, at the last table, it can automatically produce the substitute answer from min value at column D from table 1 even min value change at X1, X2 and X3..

[FDibbins]

OK I see what you want. Trythis, copied down and across...

=IF($F4=MIN($F$4:$F$6),INDEX($B$3:$F$6,MATCH(MIN($F$4:$F$6),$F$3:$F$6,0),MATCH(I$3,$B$3:$F$3,0)),I4)

[azimuiz]

Awesome, That's the answer, Thanks FDibbins

[FDibbins]

Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)