Vlookup - last match

Exceler8

New Member
Joined
May 16, 2003
Messages
33
I'm sure something like this has been discussed before... but I couldn't find anything.

I am essentially trying to do a Vlookup, but returning the last value to match, rather than the first. To be exact, here is what I have:

ID Value
1 10
2 10
3 10
4 10
1 20
3 10
1 40

I am trying to retrieve the most recent value, for the ID = 1. A conventional vlookup will give me the value "10"... but I want the value "40".

Any ideas?

TIA.
 
Hi Aladin,

I have experimented with the formula and it just shows up with #n/a - also when I try define Drange and Erange instead of ALL! It just defaults to the name of the workbook as below;


=ALL!$E$2:INDEX(ALL!$E:$E,"Book 1.xlsx'!Lrow

Dis you have any ideas as to why this is? Thanks
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Also further to this, the values in column E are letters, not numeric - is this an issue? Thanks again.
 
Upvote 0
Also further to this, the values in column E are letters, not numeric - is this an issue? Thanks again.

Let's get this straight.

1) The sheet housing the data is named ALL.
2) You want to automatically include new data.
3) As you state above, the range in column E houses letters (text), not numbers.

A question: What does the range in column D house - text or numbers?
 
Upvote 0
Let's get this straight.

1) The sheet housing the data is named ALL.
2) You want to automatically include new data.
3) As you state above, the range in column E houses letters (text), not numbers.

A question: What does the range in column D house - text or numbers?

Hi - sorry if my wording has been unclear so far, as I am quite novice at excel. This forum I must admit has been fantastic so far. Just to answer the questions that you asked.

1) The sheet housing the data is named ALL
2) I want to be able to include new data should it be updated
3) Column E houses letters. Column B houses date (i.e. numbers). Column J houses numbers. These are three columns I need to look up the second last values of.

I have attached an example file for you - hopefully this helps as to what I am looking for. This is a smaller much condensed version of the file I am dealing with. Thank You
 
Upvote 0
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {mso-number-format:"Short Date";} --> </style>Below is an example similar to the ALL sheet I am dealing with, hopefully this is of assistance

[TABLE="width: 780"]
<col style="width:65pt" span="12" width="65"> <tbody>[TR]
[TD="width: 65"]A
[/TD]
[TD="width: 65"]B[/TD]
[TD="width: 65"]C[/TD]
[TD="width: 65"]D[/TD]
[TD="width: 65"]E[/TD]
[TD="width: 65"]F[/TD]
[TD="width: 65"]G[/TD]
[TD="width: 65"]H[/TD]
[TD="width: 65"]I[/TD]
[TD="width: 65"]J[/TD]
[TD="width: 65"]K[/TD]
[TD="width: 65"]L[/TD]
[/TR]
[TR]
[TD]AA1[/TD]
[TD="class: xl63, align: right"]1/01/11[/TD]
[TD]X[/TD]
[TD]ABC[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]225[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD="class: xl63, align: right"]1/03/11[/TD]
[TD][/TD]
[TD]BBC[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]426[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA3[/TD]
[TD="class: xl63, align: right"]2/05/11[/TD]
[TD][/TD]
[TD]CNN[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]922[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA4[/TD]
[TD="class: xl63, align: right"]3/08/11[/TD]
[TD][/TD]
[TD]CBC[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]888[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA5[/TD]
[TD="class: xl63, align: right"]23/01/12[/TD]
[TD][/TD]
[TD]FOX[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]726[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA6[/TD]
[TD="class: xl63, align: right"]24/01/12[/TD]
[TD][/TD]
[TD]ABC[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]455[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA7[/TD]
[TD="class: xl63, align: right"]25/01/12[/TD]
[TD][/TD]
[TD]BBC[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]77[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA8[/TD]
[TD="class: xl63, align: right"]26/01/12[/TD]
[TD][/TD]
[TD]CNN[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]92[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA9[/TD]
[TD="class: xl63, align: right"]27/01/12[/TD]
[TD][/TD]
[TD]CBC[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]89[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA10[/TD]
[TD="class: xl63, align: right"]28/01/12[/TD]
[TD][/TD]
[TD]FOX[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]66[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA11[/TD]
[TD="class: xl63, align: right"]29/01/12[/TD]
[TD][/TD]
[TD]ABC[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]44[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA12[/TD]
[TD="class: xl63, align: right"]30/01/12[/TD]
[TD][/TD]
[TD]BBC[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]66[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA13[/TD]
[TD="class: xl63, align: right"]31/01/12[/TD]
[TD][/TD]
[TD]CNN[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA14[/TD]
[TD="class: xl63, align: right"]1/02/12[/TD]
[TD][/TD]
[TD]CBC[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]827[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA15[/TD]
[TD="class: xl63, align: right"]2/02/12[/TD]
[TD][/TD]
[TD]FOX[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]466[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA16[/TD]
[TD="class: xl63, align: right"]3/02/12[/TD]
[TD][/TD]
[TD]ABC[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]924[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA17[/TD]
[TD="class: xl63, align: right"]4/02/12[/TD]
[TD][/TD]
[TD]BBC[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]899[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA18[/TD]
[TD="class: xl63, align: right"]5/02/12[/TD]
[TD][/TD]
[TD]CNN[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]226[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA19[/TD]
[TD="class: xl63, align: right"]6/02/12[/TD]
[TD][/TD]
[TD]CBC[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]887[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA20[/TD]
[TD="class: xl63, align: right"]7/02/12[/TD]
[TD][/TD]
[TD]FOX[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And below is an example of what the sheet "Lookup" looks like

[TABLE="width: 804"]
<col span="5"><col span="2"><col span="2"><tbody>[TR]
[TD][/TD]
[TD="colspan: 4"]HEADING[/TD]
[TD="colspan: 4"]HEADING[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Second Last E
[/TD]
[TD]Second Last B[/TD]
[TD="colspan: 2"]Second Last J[/TD]
[/TR]
[TR]
[TD]BBC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CNN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CBC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FOX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank You.
 
Upvote 0
Hi - sorry if my wording has been unclear so far, as I am quite novice at excel. This forum I must admit has been fantastic so far. Just to answer the questions that you asked.

1) The sheet housing the data is named ALL
2) I want to be able to include new data should it be updated
3) Column E houses letters. Column B houses date (i.e. numbers). Column J houses numbers. These are three columns I need to look up the second last values of.

I have attached an example file for you - hopefully this helps as to what I am looking for. This is a smaller much condensed version of the file I am dealing with. Thank You

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {mso-number-format:"Short Date";} --> </style>Below is an example similar to the ALL sheet I am dealing with, hopefully this is of assistance
[...]
Thank You.

The ALL sheet, A:J...
[TABLE="width: 518"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 81pt; mso-width-source: userset; mso-width-alt: 3840;" width="108"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 53pt; mso-width-source: userset; mso-width-alt: 2531;" width="71"> <col style="width: 48pt;" span="5" width="64"> <tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl70, width: 108, bgcolor: transparent"]Date[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 71, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA1[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]1/1/2011[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]X[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]ABC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]225[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA2[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]1/3/2011[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]BBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]426[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA3[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]2/5/2011[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CNN[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]922[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA4[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]3/8/2011[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]888[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA5[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]23/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]FOX[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]726[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA6[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]24/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]ABC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]455[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA7[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]25/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]BBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]77[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA8[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]26/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CNN[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]92[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA9[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]27/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]89[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA10[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]28/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]FOX[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]66[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA11[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]29/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]ABC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]44[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA12[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]30/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]BBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]66[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA13[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]31/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CNN[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]28[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA14[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]1/2/2012[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]827[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA15[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]2/2/2012[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]FOX[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]466[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA16[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]3/2/2012[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]ABC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]924[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA17[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]4/2/2012[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]BBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]899[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA18[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]5/2/2012[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CNN[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]226[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA19[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]6/2/2012[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]887[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA20[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]7/2/2012[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]FOX[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]26[/TD]
[/TR]
</tbody>[/TABLE]

Lrow, Scope ALL:
Rich (BB code):
=MATCH(9.99999999999999E+307,ALL!$B:$B)
Brange, Scope Workbook:
Rich (BB code):
=ALL!$B$2:INDEX(ALL!$B:$B,ALL!Lrow)
Drange, Scope Workbook:
Rich (BB code):
=ALL!$D$2:INDEX(ALL!$D:$D,ALL!Lrow)
Erange, Scope Workbook:
Rich (BB code):
=ALL!$E$2:INDEX(ALL!$E:$E,ALL!Lrow)
Jrange, Scope Workbook:
Rich (BB code):

=ALL!$J$2:INDEX(ALL!$J:$J,ALL!Lrow)

The Lookup sheet, A:E...
[TABLE="width: 395"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 97pt; mso-width-source: userset; mso-width-alt: 4579;" width="129"> <col style="width: 103pt; mso-width-source: userset; mso-width-alt: 4892;" width="138"> <col style="width: 99pt; mso-width-source: userset; mso-width-alt: 4693;" width="132"> <col style="width: 48pt;" width="64"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 129, bgcolor: transparent"]Second Last E[/TD]
[TD="class: xl68, width: 138, bgcolor: transparent"]Second Last B[/TD]
[TD="class: xl68, width: 132, bgcolor: transparent"]Second Last J[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Idx[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]ABC[/TD]
[TD="class: xl66, width: 129, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 138, bgcolor: transparent"]29/01/12[/TD]
[TD="class: xl66, width: 132, bgcolor: transparent, align: right"]44[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]BBC[/TD]
[TD="class: xl66, width: 129, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 138, bgcolor: transparent"]30/01/12[/TD]
[TD="class: xl66, width: 132, bgcolor: transparent, align: right"]66[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]CNN[/TD]
[TD="class: xl66, width: 129, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 138, bgcolor: transparent"]31/01/12[/TD]
[TD="class: xl66, width: 132, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]13[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]CBC[/TD]
[TD="class: xl66, width: 129, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 138, bgcolor: transparent"]1/2/2012[/TD]
[TD="class: xl66, width: 132, bgcolor: transparent, align: right"]827[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]FOX[/TD]
[TD="class: xl66, width: 129, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 138, bgcolor: transparent"]2/2/2012[/TD]
[TD="class: xl66, width: 132, bgcolor: transparent, align: right"]466[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]

B2, copied down:
Rich (BB code):
=INDEX(Erange,$E2)
C2, copied down:
Rich (BB code):
=INDEX(Brange,$E2)
D2, copied down:
Rich (BB code):
=INDEX(Jrange,$E2)
E2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=LARGE(IF(Drange=$A2,ROW(Drange)-ROW(INDEX(Drange,1,1))+1),2)

See:
https://dl.dropbox.com/u/65698317/aaConditionalSecondLastValue%20Public%20ls23%20.xlsx
 
Upvote 0
I'd like to use the above function in Google Spreadsheet, unfortunately it does not support 'Lookup' function, any work around to achieve the same result ?

TIA

Google Spreadsheet seems to understand:

=VLOOKUP(9.99999999999999E+307,H2:H6,1,1)

if H2:H6 is numeric, and

=VLOOKUP(REPT("z",255),J2:J6,1,1)

if J2:J6 is text in that the formulas return the last numeric value and the last text value like in Excel.

However, it does not process the following as expected:

=VLOOKUP(9.99999999999999E+307,IF(G2:G6="Lion",H2:H6),1,1)

in that the IF bit is not admitted as a reference. (error: Argument must be a range.)

Having the IF bit as a separate formula creates a correct result, a reference, the foregoing VLOOKUP formulas cannot process properly as they do with the manually created references.

Conclusion: The Excel formula you want to implement does not seem possible in Google Spreadsheet. As I noted on a few occasions, Gnumeric and OpenOffice.org Calc behave the same way as Excel.
 
Upvote 0
Thanks...I just had a similar problem and was able to use this solution. I didn't have to submit a new question.
It's amazing to me everything Excel can do. IF I want something I can usually create it (along with google and MrExcel). Guess I need to use Excel more so I learn more.

The condition (A1:A10=C1) returns, after coercing, 1 for match and 0 for no match

E.g. an array like:

{0;0;0;1;0;0;1;0}


If you devide 1 by that array, 1/(A1:A10=C1) it will look like:

{#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!}

A lookup with LOOKUP formula and a lookup value greater than any value in the range will return the last numerical value i.e. the last 1, representing the last match.
 
Upvote 0
Unable to understand this, can you please give me some more accurate example for this?

I thought this was the formula I also needed but I'm hoping someone can help me tweak it just a bit. I have a similar situation in which I want a vlookup type formula that will give me the last value but the last non-zero value.

For example my table looks something like this:

name date
Colby 3/5/2009
Colby 4/9/2009
Colby 5/2/2009
Colby
Tom 3/3/2009
Tom
Tom 4/1/2009
Tom

In this case using that formula to search for Colby will result me 0 as the last Colby doesn't have a date but what I really want is the last non-zero value. Can anyone help me?
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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