If + Vlookup difficult search compare

[Parvin_B]

Hi from A15 : A27 this is the list with drop downs and dropdown are Priority level , and i have priorities. In K5 . i want to get value from list if list from A15 : A27. If list have any At risk , in K5 it must show At risk dont need to check other cells , if list doesnt contain At risk it must check Delayed , if any cell has delayed it must show delayed , no need to check others, then it need to check On-Track , Yet to start . For Complete if all are complete it need to show complete . I wrote some code but it is not working =IF(VLOOKUP(J5,B15:B27,1,0)="At Risk","At-Risk", IF(VLOOKUP(J6,B15:B27,1,0)="Delayed","Delayed", IF(VLOOKUP(J7,B15:B27,1,0)="On-Track","On-Track","x "))) can anyone help me?

 

[Fluff]

Hi & welcome to MrExcel.
How about like

=IF(COUNTIFS(B:B,J5),"At Risk",IF(COUNTIFS(B:B,J6),"Delayed",""))

 

[etaf]

can you use a countif()
as a nested IF/IFS

What cell do you want the results in - ALL IN K5
or move down the list ?

 

[JamesCanale]

MrExcelPlayground4.xlsx
	A	B	C	D	E	F	G	H	I	J	K
4	Phase	Status								Priority Level
5	Plan and Design	Complete								At Risk
6	Plan and Design	Complete								Delayed	Delayed
7	Plan and Design	Complete								On-Track
8	Plan and Design	Complete								Yet to Start
9	Plan and Design	Complete								Complete
10	Plan and Design	Complete
11	Plan and Design	Complete
12	Plan and Design	Complete
13	Plan and Design	Complete
14	Plan and Design	Complete
15	Build and test	Delayed
16	Build and test	Yet to Start
17	Build and test	On-Track
18	Build and test	On-Track
19	Build and test	On-Track
20	Build and test	On-Track
21	Build and test	On-Track
22	Build and test	On-Track
23	Build and test	On-Track
24	Build and test	On-Track
25	Build and test	Delayed
26	Build and test	Delayed
27	Build and test	On-Track
28	Communication & Training	Yet to Start
29	Communication & Training	Yet to Start
30	Communication & Training	Yet to Start
31	Communication & Training	Yet to Start
32	Communication & Training	Yet to Start
33	Communication & Training	Yet to Start
34	Deployment	Yet to Start
35	Deployment	Yet to Start
36	Deployment	Yet to Start
37	Stabilization	Yet to Start
BCA

Cell Formulas
Range	Formula
K5	K5	=IF(COUNTIF($B$5:$B$27,J5)=0,"","At Risk")
K6	K6	=IF(COUNTIF($B$5:$B$27,J6)=0,"",IF(K5="","Delayed",""))
K7	K7	=IF(COUNTIF($B$5:$B$27,J7)=0,"",IF(AND(K5="",K6=""),"On-Track",""))
K8	K8	=IF(COUNTIF($B$5:$B$27,J8)=0,"",IF(AND(K5="",K6="",K7=""),"Yet to Start",""))
K9	K9	=IF(COUNTIF($B$15:$B$27,J9)=13,"Complete","")

 

[Parvin_B]

MrExcelPlayground4.xlsx
	A	B	C	D	E	F	G	H	I	J	K
4	Phase	Status								Priority Level
5	Plan and Design	Complete								At Risk
6	Plan and Design	Complete								Delayed	Delayed
7	Plan and Design	Complete								On-Track
8	Plan and Design	Complete								Yet to Start
9	Plan and Design	Complete								Complete
10	Plan and Design	Complete
11	Plan and Design	Complete
12	Plan and Design	Complete
13	Plan and Design	Complete
14	Plan and Design	Complete
15	Build and test	Delayed
16	Build and test	Yet to Start
17	Build and test	On-Track
18	Build and test	On-Track
19	Build and test	On-Track
20	Build and test	On-Track
21	Build and test	On-Track
22	Build and test	On-Track
23	Build and test	On-Track
24	Build and test	On-Track
25	Build and test	Delayed
26	Build and test	Delayed
27	Build and test	On-Track
28	Communication & Training	Yet to Start
29	Communication & Training	Yet to Start
30	Communication & Training	Yet to Start
31	Communication & Training	Yet to Start
32	Communication & Training	Yet to Start
33	Communication & Training	Yet to Start
34	Deployment	Yet to Start
35	Deployment	Yet to Start
36	Deployment	Yet to Start
37	Stabilization	Yet to Start
BCA

Cell Formulas
Range	Formula
K5	K5	=IF(COUNTIF($B$5:$B$27,J5)=0,"","At Risk")
K6	K6	=IF(COUNTIF($B$5:$B$27,J6)=0,"",IF(K5="","Delayed",""))
K7	K7	=IF(COUNTIF($B$5:$B$27,J7)=0,"",IF(AND(K5="",K6=""),"On-Track",""))
K8	K8	=IF(COUNTIF($B$5:$B$27,J8)=0,"",IF(AND(K5="",K6="",K7=""),"Yet to Start",""))
K9	K9	=IF(COUNTIF($B$15:$B$27,J9)=13,"Complete","")

All in one cell

 

[Fluff]

Have you tried the formula I suggested in post#2?

 

[Parvin_B]

can you use a countif()
as a nested IF/IFS

What cell do you want the results in - ALL IN K5
or move down the list ?

i want all in one cell

 

[Parvin_B]

Have you tried the formula I suggested in post#2?

yes thank you it is working , but how can i do for complete for complete all cells must be complete. could you help?

 

[etaf]

Then @Fluff formula extended for all the different conditions
As you have 365 then IFS () will work
=IFS( Countif(A15 : A27,J5)>0, "At Risk",Countif(A15 : A27,J6)>0,"Delayed",Countif(A15 : A27,J7)>0,"On-Track",Countif(A15 : A27,J8)>0, "yet to start" , Countif(A15 : A27,J9)>0, "complete", TRUE , "X")

OR
=IFS( Countif(A15 : A27,J5)>0, J5,Countif(A15 : A27,J6)>0,J6,Countif(A15 : A27,J7)>0,J7,Countif(A15 : A27,J8)>0, J8, Countif(A15 : A27,J9)>0, J9, TRUE , "X")

for older versions of excel

=IF( Countif(A15 : A27,J5)>0, J5, IF(Countif(A15 : A27,J6)>0,J6, IF( Countif(A15 : A27,J7)>0,J7, IF(Countif(A15 : A27,J8)>0, J8, IF(Countif(A15 : A27,J9)>0, J9, "X")))))

for complete ALL cells have to be complete
Then
=IF( Countif(A15 : A27,J5)>0, J5, IF(Countif(A15 : A27,J6)>0,J6, IF( Countif(A15 : A27,J7)>0,J7, IF(Countif(A15 : A27,J8)>0, J8, IF(Countif(A15 : A27,J9)=13, J9, "X")))))
But its unlikely to get to complete unless all cells are complete OR cells are left blank i guess or have anything other than the previous conditions

BUT a test of the count to be 13 which will be all the cells have complete

 

[Parvin_B]

Then @Fluff formula extended for all the different conditions
As you have 365 then IFS () will work
=IFS( Countif(A15 : A27,J5)>0, "At Risk",Countif(A15 : A27,J6)>0,"Delayed",Countif(A15 : A27,J7)>0,"On-Track",Countif(A15 : A27,J8)>0, "yet to start" , Countif(A15 : A27,J9)>0, "complete", TRUE , "X")

OR
=IFS( Countif(A15 : A27,J5)>0, J5,Countif(A15 : A27,J6)>0,J6,Countif(A15 : A27,J7)>0,J7,Countif(A15 : A27,J8)>0, J8, Countif(A15 : A27,J9)>0, J9, TRUE , "X")

for older versions of excel

=IF( Countif(A15 : A27,J5)>0, J5, IF(Countif(A15 : A27,J6)>0,J6, IF( Countif(A15 : A27,J7)>0,J7, IF(Countif(A15 : A27,J8)>0, J8, IF(Countif(A15 : A27,J9)>0, J9, "X")))))

Thank you man , can i change J5 with At Risk or other values like Countif(A15 : A27, "At Risk") ?