Help returning a value if 2 conditions are met in a different row

[JDHwood]

I am working with a data set of over 1500 accounts. I need to simplify the Status column such that if a Customer has a "High" or "Low" status it will be returned in the Status_Simplified column for all matching Customers instead of "NA", "Soft", etc. Absence of a "High" or "Low" would simply be "NA". For example, for Customer ABC I want Status Simplified to be "High" for all 3 Customer ABC accounts. For Customer XYZ, Status_Simplified should be "Low".

Figured I would start with =IF(OR(B2="High",B2="Low"),B2,.......but not sure what to put for the FALSE statement. I can use a Helper column if that is needed.

Note, data will always be in order by Customers. But Status could be in any order within a customer.

Office 265

Thanks in advance

A	B	C
Customers	Status	Status_Simplified
Customer ABC	High	High
Customer ABC	NA	High
Customer ABC	Soft	High
Customer XYZ	NA	Low
Customer XYZ	Low	Low
hundreds more unique customers........

 

[DanteAmor]

Hi @JDHwood.

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

Try:

varios 05jun2023.xlsm
	A	B	C
1	Customers	Status	Status_Simplified
2	Customer ABC	High	High
3	Customer ABC	NA	High
4	Customer ABC	Soft	High
5	Customer XYZ	NA	Low
6	Customer XYZ	Low	Low
7	Customer b	NA	NA
8	Customer b	Soft	NA
9	Customer C	Soft	NA
10	Customer 123	High	High
11	Customer 123	Low	High
12	Customer 123	NA	High
Hoja6

Cell Formulas
Range	Formula
C2:C12	C2	=IF(COUNTIFS(A:A,A2,B:B,"High"),"High",IF(COUNTIFS(A:A,A2,B:B,"Low"),"Low","NA"))

Note:
If a client has High and Low, the formula, what status should it put?
The above formula puts High.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[JDHwood]

Thank you so much. I should have clarified. There should be no situation resulting in a single customer having both High and Low status. In the off chance there was I would default it to High.

 

[DanteAmor]

Thank you so much. I should have clarified. There should be no situation resulting in a single customer having both High and Low status. In the off chance there was I would default it to High.

So the formula works.
Try the formula and comment the result.

 

[JDHwood]

It works perfectly. Thank you so much!