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]

Yes you can do that.

 

[etaf]

yes, note i edited my post - re all complete

@Fluff formula is the same as mine, although not showing the > 0 , which is a logical test , if the count is 1 or more , then in the IF , that will be seen as TRUE, and a 0 count will be seen as FALSE
so a bit quicker to write i guess, dont know if its less for excel to process and so more efficient

 

[Parvin_B]

yes, note i edited my post - re all complete

@Fluff formula is the same as mine, although not showing the > 0 , which is a logical test , if the count is 1 or more , then in the IF , that will be seen as TRUE, and a 0 count will be seen as FALSE
so a bit quicker to write i guess, dont know if its less for excel to process and so more efficient

i have 1 more question how can i do if list contain "complete" and "yet to start" it must show On-track? could you help thanks

 

[etaf]

can you give the example of what you mean.

how can i do if list contain "complete" and "yet to start" it must show On-track?

not sure i understand this bit ?

 

[Fluff]

At what point in the formula should it check that?

 

[Parvin_B]

yes, note i edited my post - re all complete

@Fluff formula is the same as mine, although not showing the > 0 , which is a logical test , if the count is 1 or more , then in the IF , that will be seen as TRUE, and a 0 count will be seen as FALSE
so a bit quicker to write i guess, dont know if its less for excel to process and so more efficient

At what point in the formula should it check that?

i THINK I FOUND AND(COUNTIF('Detailed Project Plan_PS'!BD34:BD36,"Complete")>0,COUNTIF('Detailed Project Plan_PS'!BD34:BD36,"Yet to Start")>0),"On-Track"

 

[Fluff]

Glad you sorted it & thanks for the feedback.