Find lowest value with conditions

[Dani_LobP]

Hi all,

I am trying to find the way to obtain the minimum value from a range, with some conditions.

So I have column A with some list of products, and each one have a list of countries (column B). Column A therefor has many duplicates, and Column B should be unique values, per each Col A value.
Then column C has some value. And the idea is, to make a formula that will take the range of the column A, for product A (example), and then from Column C, find the lowest option (by doing a "left(x,1)" to obtain the lowest number) and apply it to the whole product A (as mentioned as example). So each Product in col A will have same value in col D, being the smallest of the range.

A	B	C	D
Product	Country	Value	Formula result example
A	AB	1 - X	1 - X
A	AC	2 - Y	1 - X
A	AD	1 - X	1 - X
B	AB	2 - Y	1 - X
B	AC	3 - Z	1 - X
B	AD	1 - X	1 - X
C	AE	3 - Z	3 - Z
C	AB	3 - Z	3 - Z

Id like to think its making sense what i tried to explain. As for now my struggle is in making the condition. I'm currently using couple columns with CountIfs to obtain numbers and then comparing them columns to check if its same (in which case, would be easy to apply same value to all range) ... and when not, then find the smaller value and apply to them all. but i think I'm overdoing it and there might be easier way or a smartest formula to do all I am doing in a shorter way.

Thanks in advance!

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	Product	Country	Value	Formula result example
2	A	AB	1 - X	1 - X
3	A	AC	2 - Y	1 - X
4	A	AD	1 - X	1 - X
5	B	AB	2 - Y	1 - Y
6	B	AC	3 - Z	1 - Y
7	B	AD	1 - Y	1 - Y
8	C	AE	3 - Z	3 - Z
9	C	AB	3 - Z	3 - Z
Master

Cell Formulas
Range	Formula
D2:D9	D2	=LET(s,SORTBY($A$2:$C$9,LEFT($C$2:$C$9,1)+0),TAKE(FILTER(INDEX(s,,3),INDEX(s,,1)=A2),1))

 

[Dani_LobP]

How about

Fluff.xlsm
	A	B	C	D
1	Product	Country	Value	Formula result example
2	A	AB	1 - X	1 - X
3	A	AC	2 - Y	1 - X
4	A	AD	1 - X	1 - X
5	B	AB	2 - Y	1 - Y
6	B	AC	3 - Z	1 - Y
7	B	AD	1 - Y	1 - Y
8	C	AE	3 - Z	3 - Z
9	C	AB	3 - Z	3 - Z
Master

Cell Formulas
Range	Formula
D2:D9	D2	=LET(s,SORTBY($A$2:$C$9,LEFT($C$2:$C$9,1)+0),TAKE(FILTER(INDEX(s,,3),INDEX(s,,1)=A2),1))

thanks for your help once again!
hmm but not sure how to test it.
Reality is my column A in example is Column B in my table. Column B is my column E, (actually can ignore B from the formula cuz what matters is the number of rows for Product and Values... ). Column C is AV in my table. So unsure how to convert what you proposed in my scenario :D seems complex.

 

[Fluff]

How about

=LET(s,SORTBY($B$2:$AV$9,LEFT($AV$2:$AV$9,1)+0),TAKE(FILTER(TAKE(s,,-1),INDEX(s,,1)=B2),1))

 

[Dani_LobP]

How about

=LET(s,SORTBY($B$2:$AV$9,LEFT($AV$2:$AV$9,1)+0),TAKE(FILTER(TAKE(s,,-1),INDEX(s,,1)=B2),1))

Thanks a lot! that will make it... i understand better now the formula too after you made the changes to adapt.
thanks!

 

[Fluff]

You're welcome & thanks for the feedback.