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>

 

[SyedUsman]

=INDEX($C$4:$P$4,LARGE(COLUMN($C$4:$P$4)-2,LARGE({5,4,3,2,1},COLUMNS($C$4:C4))))

Paste the code in the first cell & drag to the next four , Use Ctrl Shift Enter

 

[tinferns]

Thanks for your reply SyedUsman.. However, it is not fully functional. For eg, if I go back in time like when I only update week1 score then the result is 0. Similarly when I update Week2 results, it still shows 0. Only when I update Week 3 reults, then it gives me the result. I have to update Week 1 - Week 7 and I want the formula to show final result auto updated every week.. Please update formula and advise. Thanks a million in advance.

 

[SyedUsman]

=index($c$4:$w$4,large(column($c$4:$w$4)-2,large({6,5,4,3,2},columns($c$4:c4))))

use with cse

i believe you are entering a column while adding the data & you will enter the column at column w thereby extending the range in the formula

 

[Peter_SSs]

See if this does what you want. This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across and down.

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2	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]
3	G	R	A	G	G	A	A	R	A	G	R	G	A	G	G		R	G	A	G	G
4	G	R	A	G	G	A	A	R	A	G							A	A	R	A	G
Last

 

[tinferns]

Thanks a million Peter... this formula seems to work fine.. just two questions

1) What does "Z" stand for ?
2) Guess I have to paste the formula in Column S. And then drag it ..

Please lemme know. Thanks...

See if this does what you want. This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across and down.

Last

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

2

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]

3

G

R

A

G

G

A

A

R

A

G

R

G

A

G

G

R

G

A

G

G

4

G

R

A

G

G

A

A

R

A

G

A

A

R

A

G

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:84px;"><col style="width:49px;"><col style="width:66px;"><col style="width:66px;"><col style="width:75px;"><col style="width:84px;"><col style="width:49px;"><col style="width:66px;"><col style="width:66px;"><col style="width:75px;"><col style="width:84px;"><col style="width:49px;"><col style="width:66px;"><col style="width:66px;"><col style="width:75px;"><col style="width:92px;"><col style="width:84px;"><col style="width:49px;"><col style="width:66px;"><col style="width:66px;"><col style="width:75px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula S3 {=LOOKUP("Z",IF($C$2:$Q$2=S$2,$C3:$Q3))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[tinferns]

Hi SyedUsman.. Thanks for your reply. However I have failed in using it. Peter has given a suggestion, which seems to work fine. Thanks a million for your effort and suggestions.. Have a nice day mate.. Cheers...

=index($c$4:$w$4,large(column($c$4:$w$4)-2,large({6,5,4,3,2},columns($c$4:c4))))

use with cse

i believe you are entering a column while adding the data & you will enter the column at column w thereby extending the range in the formula

 

[Peter_SSs]

Thanks a million Peter... this formula seems to work fine..

Good news.

just two questions

1) What does "Z" stand for ?
2) Guess I have to paste the formula in Column S. And then drag it ..

1) "Z" is just the letter "Z". All your letters are lower in the alphabet than "Z" so when the LOOKUP cannot find a "Z" it returns the last letter found (with the correct heading) in the row.

2) Yes, as my previous post said: Drag it across and down.

 

[tinferns]

Thanks Peter for that quick reply, appreciate.....

I modified the formula only to work if that salesman is ACTIVE, however it gives me an error #VALUE ! ... can you please correct me

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

Thank you in advance...

Good news.


1) "Z" is just the letter "Z". All your letters are lower in the alphabet than "Z" so when the LOOKUP cannot find a "Z" it returns the last letter found (with the correct heading) in the row.

2) Yes, as my previous post said: Drag it across and down.

 

[Peter_SSs]

I modified the formula only to work if that salesman is ACTIVE, however it gives me an error #VALUE ! ... can you please correct me

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

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)),"")}