Lowest value in columns and List of lowest values

[hpernaf]

Hi everyone.

I have a list of products with prices from 4 different markets.

I need a formula that when I select a product in H3, the value of that product in H4 is shown and in H5 the respective market that contains this smaller product is shown.

In another spreadsheet, would it be possible to export the lowest prices for each product and the respective markets where these products are sold? Something like the image below:

Attached I am leaving the spreadsheet. In case anyone can help me, I will be very grateful.

Example.xlsx

drive.google.com

 

[sandy666]

you can try Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Product"}, "Attribute", "Value")
in
    UOC

then Pivot Table

Product	Market 1	Market 2	Market 3	Market 4		Product	Watermelon
Grape	$2.00	$2.50	$3.00	$3.50
Apple	$1.00	$1.20	$1.40	$2.50		Market	Min of Value
Banana	$1.00	$4.00	$3.00	$3.00		Market 1	3
Orange	$3.00	$3.00	$1.00	$2.30		Market 2	1
Watermelon	$3.00	$1.00	$1.70	$1.80		Market 3	1.7
						Market 4	1.8

s666-PQ-PT-hpernaf.xlsx

Microsoft Excel Workbook

1drv.ms

 

[hpernaf]

Thank you for your reply
Any way to do this without using Power Query?
I'm using Excel 2013 and I don't have access to Power Query

 

[sandy666]

PowerQuery add-in for XL 2010/2013, all above has PQ built-in
update your profile about Excel version

probably there is another way but I don't know (too lazy rather )

 

[hpernaf]

OK.
I'll wait. It may be that someone else will answer me.
Thankful.

 

[sandy666]

You are welcome

update your profile about Excel version