Lookup function to return second last value

Nagaraj475

New member
Joined
Apr 6, 2014
Messages
29
Reaction score
0
Points
0
Location
India
Excel Version(s)
Excel2010
Hello all,


I use the attached file for documenting my monitoring observations. In previous Coaching follow up I need the value from last 'Action Plan' for the same agent. The data is in Sheet 2. I tried various formulas but was successful only in getting the current coaching observation. I need the last coaching observation populated in the cell highlighted in green.

Thanks & regards,
Nagaraj
 

Attachments

  • Aug14Copy.xls
    140 KB · Views: 16
Try this array formula

=INDEX('Sheet1 (2)'!$E$1:$E$309,MAX(IF('Sheet1 (2)'!$B$1:$B$309=D7,ROW('Sheet1 (2)'!$B$1:$B$309))))
 
Hello Bob,

Thank you for your time and help. The formula returns the first value, I'm looking forward to get last second value. To give a general idea, when I document coaching observation, while in coaching session, I need to recap last coaching session's observations. One particular example, If my current coaching document is 616203140, the observation should be abc9 which in cell E9. Hope you and other members of this forum will be able to help me with this.

Thank you very much. Appreciate your time.

Thanks,
Nagaraj
 
Did you commit Bob's formula with Ctrl+Shift+Enter?

Hi,

Thank you for your interest in helping. I tried hitting the key combination after entering the formula, but it's yielding the same result. It's getting me the first value for the record I'm looking for. For emp ID 31002, the value I'm expecting is abc9, which is the second last value. Request further assistance in solving this puzzle.

Thanks & Regards,
Nagaraj
 
According to your data, there is an entry of abc18 on 6th Aug for 31002, and that is what my formula returns (after tidying up all your data errors).
 
According to your data, there is an entry of abc18 on 6th Aug for 31002, and that is what my formula returns (after tidying up all your data errors).

Hi Bob,

Thank you so much for your time and efforts to help me. Sorry, for the data errors that were not worked before sending the file. May be I'm missing a step, the formula is getting me the last value of coaching observations. The observations I get with this formula are for the last record I'm working. I need the value of the second last record, so that I can recap previous coaching session's observations.

Thanks again for your time.

Regards,
Nagaraj
 
The originnal post said last record. If you want second last, try this array formula

=INDEX('Sheet1 (2)'!$E$1:$E$309,LARGE(IF('Sheet1 (2)'!$A$1:$A$309=C8,ROW('Sheet1 (2)'!$B$1:$B$309)),2))

It will fail if there is only one.
 
The originnal post said last record. If you want second last, try this array formula

=INDEX('Sheet1 (2)'!$E$1:$E$309,LARGE(IF('Sheet1 (2)'!$A$1:$A$309=C8,ROW('Sheet1 (2)'!$B$1:$B$309)),2))

It will fail if there is only one.

Hi Bob,

I'm sorry, I'm not able to figure out where I'm missing. I'm not getting the second last value. Can you please share the worked copy of the file you have?

Many thanks,
Nagaraj
 
Back
Top