Suggestion needed to return a value from a range .

I have a spreadsheet which contains a price list for material sizes
with multiple pricing. ie.

MATERIAL COMP_A COMP_B COMP_C COMP_D COMP_E
SIZE PRICE PRICE PRICE PRICE PRICE
3/16 X 1.5 1.95
3/16 X 2 2.20 1.18 1.17 1.26 1.46
3/16 X 2.5 2.47 1.55 1.48 1.68
3/16 X 3 2.90 1.90 1.86 2.14

Based on a particular size, I would like to return the least expensive
material cost and the company who supplies it. ie.

If 3/16 X 2 were chosen from the dropdown list then
1.17 is returned in one cell and COMP_C returned in another.

MATERIAL SIZE PRICE COMPANY
3/16 X 2 1.17 COMP_C

I already have the prices being returned correcty using the vlookup
function but cannot get the company name to be returned.

I will be happy to supply more information if needed.
Thanks for any help.

j_witt wrote:
> I have a spreadsheet which contains a price list for material sizes
> with multiple pricing. ie.
>
> MATERIAL COMP_A COMP_B COMP_C COMP_D COMP_E
> SIZE PRICE PRICE PRICE PRICE PRICE
> 3/16 X 1.5 1.95
> 3/16 X 2 2.20 1.18 1.17 1.26 1.46
> 3/16 X 2.5 2.47 1.55 1.48 1.68
> 3/16 X 3 2.90 1.90 1.86 2.14
>
> Based on a particular size, I would like to return the least expensive
> material cost and the company who supplies it. ie.
>
> If 3/16 X 2 were chosen from the dropdown list then
> 1.17 is returned in one cell and COMP_C returned in another.
>
> MATERIAL SIZE PRICE COMPANY
> 3/16 X 2 1.17 COMP_C
>
> I already have the prices being returned correcty using the vlookup
> function but cannot get the company name to be returned.
>
> I will be happy to supply more information if needed.
> Thanks for any help.
>
You could add two coloumns in your table:


First a column that picks the lowest value of the row with =MIN(A2:F2)
Then a column that reports back the header of the column with that value
=INDEX(A1:F1;;MATCH(G2;A2:F2;0))

Then you use ordinary lookup to identify the lowest price and its column.

That worked - Thanks!

That worked - Thanks!