Help on HLook up

T

tinferns

Board Regular
Joined
Aug 18, 2009
Messages
150
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
[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]
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Code: 
=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
 
Upvote 0
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.
 
Upvote 0
Code: 
=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
 
Upvote 0
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
CDEFGHIJKLMNOPQRSTUVW
2Frequent AbsenteeismComm. SkillAttrition probabilityBehavioral ConcernsTraining [Scalability]Frequent AbsenteeismComm. SkillAttrition probabilityBehavioral ConcernsTraining [Scalability]Frequent AbsenteeismComm. SkillAttrition probabilityBehavioral ConcernsTraining [Scalability]Frequent AbsenteeismComm. SkillAttrition probabilityBehavioral ConcernsTraining [Scalability]
3GRAGGAARAGRGAGGRGAGG
4GRAGGAARAGAARAG
Last
 
Last edited:
Upvote 0
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...

Peter_SSs said:
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

CDEFGHIJKLMNOPQRSTUVW
Frequent AbsenteeismComm. SkillAttrition probabilityBehavioral ConcernsTraining [Scalability]Frequent AbsenteeismComm. SkillAttrition probabilityBehavioral ConcernsTraining [Scalability]Frequent AbsenteeismComm. SkillAttrition probabilityBehavioral ConcernsTraining [Scalability] Frequent AbsenteeismComm. SkillAttrition probabilityBehavioral ConcernsTraining [Scalability]
GRAGGAARAGRGAGG RGAGG
GRAGGAARAG AARAG

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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
Click to expand...
 
Upvote 0
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...

SyedUsman said:
Code: 
=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
Click to expand...
 
Upvote 0
tinferns said:
Thanks a million Peter... this formula seems to work fine..
Click to expand...
Good news. :)


tinferns said:
just two questions

1) What does "Z" stand for ?
2) Guess I have to paste the formula in Column S. And then drag it ..
Click to expand...
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.
 
Upvote 0
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...



Peter_SSs said:
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.
Click to expand...
 
Upvote 0
tinferns said:
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),""))}
Click to expand...
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)),"")}
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
Ironman
  • Question Question
Review of Application.EnableEvents = False/True lines in worksheet_change event
Replies
1
Views
2K
jasonb75
J
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A
Oakie_Uffda
  • Question Question
Split Cell into multiple without splitting words and print decent
Replies
1
Views
1K
Oakie_Uffda
Oakie_Uffda
H
  • Question Question
Dynamic Range VBA
Replies
1
Views
481
PatOBrien198
PatOBrien198

Forum statistics

Threads
1,224,749
Messages
6,180,727
Members
452,995
Latest member
isldboy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top