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:

Product	12	24	52	48
Apple		2	4	43
Orange	2		6	56
Pear	1		9
Grapes		3	14	24

<tbody>
</tbody>

Sheet 2:

12	1	Pear
24	2	Apple
48	24	Grapes
52	4	Apple

<tbody>
</tbody>

 

[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

Row\Col	A​	B​	C​	D​	E​
1​	Product	12​	24​	52​	48​
2​	Apple		2​	4​	43​
3​	Orange	2​		6​	56​
4​	Pear	1​	2​	9​
5​	Grapes		3​	14​	24​

Sheet2

Row\Col	A​	B​	C​	D​	E​
1​
2​	12​	1​	Pear
3​	24​	2​	Apple	Pear
4​	48​	24​	Grapes
5​	52​	4​	Apple
6​

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))),"")