Help on HLook up

[tinferns]

Hi, Need your help in obtaining the last updated cell in excel. Here I am grading salesman over 3 - 7 weeks (only 3 weeks shown here). Can we work out a formula that will give me result of the last updated week. I have given the expected answer. Please advise if you want me to attach an excel sheet example. Could not trace a way to attach here. I prefer a formula instead of a VBA code.

<colgroup><col><col><col span="22"></colgroup><tbody>
</tbody>

Week 1

Week 2

Week 3

Current Status

Sales Man

. Comments .

Frequent Absenteeism

Comm. Skill

Attrition probability

Behavioral Concerns

Training [Scalability]

Frequent Absenteeism

Comm. Skill

Attrition probability

Behavioral Concerns

Training [Scalability]

Frequent Absenteeism

Comm. Skill

Attrition probability

Behavioral Concerns

Training [Scalability]

Frequent Absenteeism

Comm. Skill

Attrition probability

Behavioral Concerns

Training [Scalability]

Name

Free text

G

R

A

G

G

A

A

R

A

G

R

G

A

G

G

Expected Answer: should be last update cell-:

G = Green

R

G

A

G

G

A = Amber

R = Red

await your reply..

<colgroup><col><col><col span="22"></colgroup><tbody>
</tbody>

 

[tinferns]

Perfecto Peter... it worked fine.. Thanks a zillion for all your help. Have a great day ahead.. Cheers...

You are on the right track but there are 3 issues
1. You need a $ sign in from of B3 to lock that to column B when you drag across.
2. You have a comma where you should have an = sign
3. You have the ,"" near the end in the wrong place

Try
{=IF($B3="Active",LOOKUP("Z",IF($C$2:$V$2=Y$2,$C3:$V3)),"")}

 

[Peter_SSs]

Perfecto Peter... it worked fine.. Thanks a zillion for all your help. Have a great day ahead.. Cheers...

Glad to help. .. and thanks.