Return text in cell from value in another cell

[mvassallo]

Hi All,
I need assistance is solving this one. I'm a good excel user but this one is baffling me.
I work with MS Excel 2003. I have a table with Material Description and Prices for 6 different types.
I would like to have a formula returning the Material and Type from the value I input in a cell. Have tried with sumproduct, Index and Match but, hey, no success. I attach a copy of the file so that I may get the right assistance from the right people. The attached file is a small sample of my actual file which is very big.

Thanks to all
M

[:) Sixthsense :)]

What is the expected result of D15 and C15?

[paraxis]

It's got to be searched from the Master Table and in my case it should be B15=PVC and C15=Type 1.
M

[Izandol]

You may try:
B15: =INDEX($B$3:$B$7,MIN(IF($C$3:$H$7=$D15,ROW(C3:H7)-2)))
C15: =INDEX($C$2:$H$2,MIN(IF($C$3:$H$7=$D15,COLUMN($C$3:$H$7)-2)))

both formulas must be entered with Ctrl+Shift+Enter

[Pepe Le Mokko]

Does the attached help (non array formulas) ? Of course if there are two identical values....

[:) Sixthsense :)]

In B15 Cell

=INDEX(B3:B7,SUMPRODUCT(1*MAX((C3:H7=D15)*ROW(C3:H7)))-MIN(ROW(C3:H7))+1)

In C15 Cell

=INDEX(C2:H2,,SUMPRODUCT(1*MAX((C3:H7=D15)*COLUMN(C3:H7)))-MIN(COLUMN(C3:H7))+1)