if condition with max

[ravi2628]

Hi team,

good morning/afternoon/evening/night

i required a small output with following data.

Input

Book2
	A	B	C
1	Product	Seller	Price
2	Apple	Dmart	499
3	Apple	Metro	366
4	Apple	Reliance	110
5	Apple	Local Store	197
6	Orange	Metro	497
7	Orange	Reliance	166
8	Orange	Local Store	495
9	Grapes	Dmart	158
10	Grapes	Metro	134
11	Grapes	Reliance	138
12	Grapes	Local Store	449
Sheet1

Output

Book2
	A	B	C	D
1	Product	Seller	Price	Price Variant
2	Apple	Dmart	499	Higest Price
3	Apple	Metro	366	Lowest Price
4	Apple	Reliance	110	Lowest Price
5	Apple	Local Store	197	Lowest Price
6	Orange	Metro	497	Higest Price
7	Orange	Reliance	166	Lowest Price
8	Orange	Local Store	495	Lowest Price
9	Grapes	Dmart	158	Lowest Price
10	Grapes	Metro	134	Lowest Price
11	Grapes	Reliance	138	Lowest Price
12	Grapes	Local Store	449	Higest Price
Sheet1

Please Check and help me out with the formula.

Regards,
Ravi

 

[mrshl9898]

=IF(MAXIFS(C:C,A:A,A2)=C2,"Highest ", "Lowest ")&"Price"

 

[ravi2628]

=IF(MAXIFS(C:C,A:A,A2)=C2,"Highest ", "Lowest ")&"Price"

Hi

as per my pervious data you answer worked but not i was having a issue in the data,please can you help with that

Book2
	A	B	C	D	E
1	Product	Seller	Price	Price Variant	Vendor Segment
2	Apple	Dmart	499	Higest Price	Higest
3	Apple	Dmart	346	Lowest Price	Higest
4	Apple	Dmart	223	Lowest Price	Higest
5	Apple	Metro	366	Lowest Price	Lowest
6	Apple	Reliance	110	Lowest Price	Lowest
7	Apple	Local Store	197	Lowest Price	Lowest
8	Orange	Metro	497	Higest Price	Higest
9	Orange	Reliance	166	Lowest Price	Lowest
10	Orange	Local Store	495	Lowest Price	Lowest
11	Grapes	Dmart	158	Lowest Price	Lowest
12	Grapes	Metro	134	Lowest Price	Lowest
13	Grapes	Reliance	138	Lowest Price	Lowest
14	Grapes	Local Store	449	Higest Price	Higest
Sheet1

 

[mrshl9898]

Sorry, not sure what you need. What is the new column and which results are incorrect?

 

[Peter_SSs]

If each group of Product/Seller is sorted from highest price to lowest price like Apple/Dmart then perhaps this?

If this is not it can you
- Try to clarify further and
- Tell us whether this needs to work in both Excel 2019 and 365, or whether just 365 would be sufficient.

21 05 24.xlsm
	A	B	C	D	E
1	Product	Seller	Price	Price Variant	Vendor Segment
2	Apple	Dmart	499	Higest Price	Highest
3	Apple	Dmart	346	Lowest Price	Highest
4	Apple	Dmart	223	Lowest Price	Highest
5	Apple	Metro	366	Lowest Price	Lowest
6	Apple	Reliance	110	Lowest Price	Lowest
7	Apple	Local Store	197	Lowest Price	Lowest
8	Orange	Metro	497	Higest Price	Highest
9	Orange	Reliance	166	Lowest Price	Lowest
10	Orange	Local Store	495	Lowest Price	Lowest
11	Grapes	Dmart	158	Lowest Price	Lowest
12	Grapes	Metro	134	Lowest Price	Lowest
13	Grapes	Reliance	138	Lowest Price	Lowest
14	Grapes	Local Store	449	Higest Price	Highest
High Low

Cell Formulas
Range	Formula
E2:E14	E2	=IF(C2=MAXIFS(C$2:C$14,A$2:A$14,A2),"Highest",IF(AND(A2=A1,B2=B1),E1,"Lowest"))