Help on HLook up

[tinferns]

[TABLE="width: 1751"]
<colgroup><col><col><col span="22"></colgroup><tbody>[TR]
[TD]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. [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1751"]
<colgroup><col><col><col span="22"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5, align: center"]Week 1[/TD]
[TD="colspan: 5, align: center"]Week 2[/TD]
[TD="colspan: 5, align: center"]Week 3[/TD]
[TD][/TD]
[TD="colspan: 5, align: center"]Current Status[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales Man[/TD]
[TD]. Comments .[/TD]
[TD]Frequent
Absenteeism[/TD]
[TD]Comm.
Skill [/TD]
[TD]Attrition
probability[/TD]
[TD]Behavioral
Concerns[/TD]
[TD]Training
[Scalability][/TD]
[TD]Frequent
Absenteeism[/TD]
[TD]Comm.
Skill [/TD]
[TD]Attrition
probability[/TD]
[TD]Behavioral
Concerns[/TD]
[TD]Training
[Scalability][/TD]
[TD]Frequent
Absenteeism[/TD]
[TD]Comm.
Skill [/TD]
[TD]Attrition
probability[/TD]
[TD]Behavioral
Concerns[/TD]
[TD]Training
[Scalability][/TD]
[TD][/TD]
[TD]Frequent
Absenteeism[/TD]
[TD]Comm.
Skill [/TD]
[TD]Attrition
probability[/TD]
[TD]Behavioral
Concerns[/TD]
[TD]Training
[Scalability][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Free text[/TD]
[TD]G[/TD]
[TD]R[/TD]
[TD]A[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]R[/TD]
[TD]A[/TD]
[TD]G[/TD]
[TD]R[/TD]
[TD]G[/TD]
[TD]A[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Expected Answer: should be last update cell-:[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]G = Green [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]G[/TD]
[TD]A[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A = Amber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]R = Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

await your reply.. [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.