Match value on another sheet and return the minimum

[nuke_twidget]

I have a few sheets I'm working with and I'm having a hard time adapting an index/match formula to meet my needs. On Sheet 1 is a table with a number of different numbers in the header with a number of other values in the rows below. On Sheet 2, there is a vertical row corresponding to the header values from the table on Sheet 1. I need a formula to return the minimum value from column with the matched value from Sheet 1, ignoring cells with blanks so they don't return zeroes. After that, I will need another formula to match that minimum value to a product in yet another column. Below is an example with the correct values in Sheet 2. Thanks in advance for any and all help.

Sheet 1:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]12[/TD]
[TD]24[/TD]
[TD]52[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]14[/TD]
[TD]24[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2:

[TABLE="width: 500"]
<tbody>[TR]
[TD]12[/TD]
[TD]1[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]2[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]24[/TD]
[TD]Grapes[/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD]4[/TD]
[TD]Apple[/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

B8: =MIN(INDEX(B$2:E$5,,MATCH(A8,B$1:E$1,)))
C8: =INDEX(A$2:A$5,MATCH(B8,INDEX(B$2:E$5,,MATCH(A8,B$1:E$1,)),))

Excel 2010
	A	B	C	D	E
1	Product	12	24	52	48
2	Apple		2	4	43
3	Orange	2		6	56
4	Pear	1		9
5	Grapes		3	14	24
6
7
8	12	1	Pear
9	24	2	Apple
10	48	24	Grapes
11	52	4	Apple
Sheet1

 

[Aladin Akyurek]

Sheet1

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td]Product[/td][td]

12​

[/td][td]

24​

[/td][td]

52​

[/td][td]

48​

[/td][/tr]
[tr][td]

2​

[/td][td]Apple[/td][td][/td][td]

2​

[/td][td]

4​

[/td][td]

43​

[/td][/tr]
[tr][td]

3​

[/td][td]Orange[/td][td]

2​

[/td][td][/td][td]

6​

[/td][td]

56​

[/td][/tr]
[tr][td]

4​

[/td][td]Pear[/td][td]

1​

[/td][td]

2​

[/td][td]

9​

[/td][td][/td][/tr]
[tr][td]

5​

[/td][td]Grapes[/td][td][/td][td]

3​

[/td][td]

14​

[/td][td]

24​

[/td][/tr]
[/table]

Sheet2

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

12​

[/td][td]

1​

[/td][td]Pear[/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td]

24​

[/td][td]

2​

[/td][td]Apple[/td][td]Pear[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

48​

[/td][td]

24​

[/td][td]Grapes[/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

52​

[/td][td]

4​

[/td][td]Apple[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In B2 enter and copy down:

=MIN(INDEX(Sheet1!$B$2:$E$5,0,MATCH($A2,Sheet1!$B$1:$E$1,0)))

In C2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet1!$A$2:$A$5,SMALL(IF(INDEX(Sheet1!$B$2:$E$5,0,MATCH($A2,Sheet1!$B$1:$E$1,0))=$B2,ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),COLUMNS($C2:C2))),"")