Vlookup - last match

[Exceler8]

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.

 

[ls23]

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

 

[ls23]

Also further to this, the values in column E are letters, not numeric - is this an issue? Thanks again.

 

[Aladin Akyurek]

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?

 

[ls23]

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

 

[ls23]

<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-ignoreadding; 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

A	B	C	D	E	F	G	H	I	J	K	L
AA1	1/01/11	X	ABC	Y					225
AA2	1/03/11		BBC	N					426
AA3	2/05/11		CNN	N					922
AA4	3/08/11		CBC	Y					888
AA5	23/01/12		FOX	N					726
AA6	24/01/12		ABC	Y					455
AA7	25/01/12		BBC	N					77
AA8	26/01/12		CNN	Y					92
AA9	27/01/12		CBC	N					89
AA10	28/01/12		FOX	Y					66
AA11	29/01/12		ABC	N					44
AA12	30/01/12		BBC	N					66
AA13	31/01/12		CNN	N					28
AA14	1/02/12		CBC	N					827
AA15	2/02/12		FOX	Y					466
AA16	3/02/12		ABC	Y					924
AA17	4/02/12		BBC	Y					899
AA18	5/02/12		CNN	N					226
AA19	6/02/12		CBC	N					887
AA20	7/02/12		FOX	Y					26

<col style="width:65pt" span="12" width="65"> <tbody>
</tbody>

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

	HEADING	HEADING
ABC					Second Last E	Second Last B	Second Last J
BBC
CNN
CBC
FOX

<col span="5"><col span="2"><col span="2"><tbody>
</tbody>

Thank You.

 

[Aladin Akyurek]

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-ignoreadding; 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...

	Date
AA1	1/1/2011	X	ABC	Y					225
AA2	1/3/2011		BBC	N					426
AA3	2/5/2011		CNN	N					922
AA4	3/8/2011		CBC	Y					888
AA5	23/01/12		FOX	N					726
AA6	24/01/12		ABC	Y					455
AA7	25/01/12		BBC	N					77
AA8	26/01/12		CNN	Y					92
AA9	27/01/12		CBC	N					89
AA10	28/01/12		FOX	Y					66
AA11	29/01/12		ABC	N					44
AA12	30/01/12		BBC	N					66
AA13	31/01/12		CNN	N					28
AA14	1/2/2012		CBC	N					827
AA15	2/2/2012		FOX	Y					466
AA16	3/2/2012		ABC	Y					924
AA17	4/2/2012		BBC	Y					899
AA18	5/2/2012		CNN	N					226
AA19	6/2/2012		CBC	N					887
AA20	7/2/2012		FOX	Y					26

<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>
</tbody>

Lrow, Scope ALL:

=MATCH(9.99999999999999E+307,ALL!$B:$B)

Brange, Scope Workbook:

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

Drange, Scope Workbook:

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

Erange, Scope Workbook:

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

Jrange, Scope Workbook:

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

The Lookup sheet, A:E...

	Second Last E	Second Last B	Second Last J	Idx
ABC	N	29/01/12	44	11
BBC	N	30/01/12	66	12
CNN	N	31/01/12	28	13
CBC	N	1/2/2012	827	14
FOX	Y	2/2/2012	466	15

<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>
</tbody>

B2, copied down:

=INDEX(Erange,$E2)

C2, copied down:

=INDEX(Brange,$E2)

D2, copied down:

=INDEX(Jrange,$E2)

E2, control+shift+enter, not just enter, and copy down:

=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

 

[jeffreytang]

Hi,

If your lookup value is in C1, try:

=LOOKUP(2,1/(A1:A10=C1),B1:B10)

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

 

[Aladin Akyurek]

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.

 

[beastman59]

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.

 

[Pradeepkankarwal]

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?