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.

 

[suzynettie]

I had to sign up on the msg board just so I could say thank you. Fairwinds, that formula is awesome. I've been trying to figure this out for an hour. Once I plugged in your formula, I had a total geek moment where I wanted to share this with all of the other analysts (unfortunately, they don't care... LOL)! Thanks again!

 

[ls23]

Sorry to resurrect an old thread but I was wondering if there was a way to adapt this formula to lookup the second last value in a table. Thank You.

 

[Aladin Akyurek]

Sorry to resurrect an old thread but I was wondering if there was a way to adapt this formula to lookup the second last value in a table. Thank You.

Consider...

[TABLE="width: 319"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 53pt; mso-width-source: userset; mso-width-alt: 2531;" width="71"> <col style="width: 48pt;" width="64"> <col style="width: 85pt; mso-width-source: userset; mso-width-alt: 4039;" span="2" width="114"> <tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]ID[/TD]
[TD="class: xl63, width: 71, bgcolor: transparent"]Value[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 114, bgcolor: transparent"]ID[/TD]
[TD="class: xl64, width: 114, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]Last Value[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]40[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]Second Last Value[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


E1 houses an ID of interest.

E2, control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,
  IF($A$2:$A$9=E1,IF(ISNUMBER($B$2:$B$9),$B$2:$B$9)))

Equivalently, just enter:

=LOOKUP(9.99999999999999E+307,
  1/(ISNUMBER($B$2:$B$9)*($A$2:$A$9=E1)),$B$2:$B$9)

E3, control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,
  $B$2:INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=E1,
    ROW($A$2:$A$9)-ROW($A$2)+1),COUNTIF($A$2:$A$9,E1)-1)))

 

[ls23]

Thanks so much for the code. I was wondering though, is the code possible for looking up something in a separate sheet. I tried adapting the code as follows to look up values in a column in another sheet called ALL but it did not work and just returned #NAME - any ideas on how best to achieve this?

Also, can I look up values in a column by just stating $B:$B in case I add values to the other sheet at a later date?

Thank You in advance.

 

[Aladin Akyurek]

Thanks so much for the code. I was wondering though, is the code possible for looking up something in a separate sheet. I tried adapting the code as follows to look up values in a column in another sheet called ALL but it did not work and just returned #NAME - any ideas on how best to achieve this?

Also, can I look up values in a column by just stating $B:$B in case I add values to the other sheet at a later date?

Thank You in advance.

So we have a sheet called ALL. What is the condition range, what is the numeric value range, and in which cell is the condition located? And what is the version of Excel you are on?

 

[ls23]

So we have a sheet called ALL. What is the condition range, what is the numeric value range, and in which cell is the condition located? And what is the version of Excel you are on?

Hi,

- The cell the condition is located in is A3 in a sheet called "Lookup" - it is looking up a three letter code located in A3. Then I will look up a three letter code in A4, A5, A6 and so on.
- I am looking up values in the sheet ALL! - The values I want to look up are in Column E, and the condition range is in Column D (i.e. corresponding with the cell the condition is located in in the sheet Lookup).
- Ideally, I would like the formula to look up anything in that entire column in the sheet ALL! - so that if new values are added into the future, I would not have to update the formula in the sheet "Lookup"
- I am using Microsoft Excel 2007

Thanks in advance

 

[Aladin Akyurek]

Hi,

- The cell the condition is located in is A3 in a sheet called "Lookup" - it is looking up a three letter code located in A3. Then I will look up a three letter code in A4, A5, A6 and so on.
- I am looking up values in the sheet ALL! - The values I want to look up are in Column E, and the condition range is in Column D (i.e. corresponding with the cell the condition is located in in the sheet Lookup).
- Ideally, I would like the formula to look up anything in that entire column in the sheet ALL! - so that if new values are added into the future, I would not have to update the formula in the sheet "Lookup"
- I am using Microsoft Excel 2007

Thanks in advance

Define Lrow by means of Formulas | Name Manager with Scope set to the ALL sheet as referring to:

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

Note that column E on ALL is assumed to be numeric. If column D is the one that is the most complete, install the following formula instead:

=MATCH(REPT("z",255),ALL!$E:$E)

Define Drange with Scope set to Workbook as referring to:

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

The definition assumes that the data (not the header if any) starts at the second row of column D.

Define likewise Erange with Scope set to Workbook as referring to:

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

Now we can invoke in the Lookup sheet the formulas, stipulated earlier, using the foregoing definitions, which allows you a dynamic set up. The formulas will include any changes to the Drange and Erange automatically...

For the conditional last numeric value, control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,IF(Drange=A3,IF(ISNUMBER(Erange),Erange)))

If Erange does not contain empty or blank cells, we cam shorten the foregoing to:

=LOOKUP(9.99999999999999E+307,IF(Drange=A3,Erange))

For the conditional second last numeric value, control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,
  INDEX(Erange,1,1):INDEX(Erange,SMALL(IF(Drange=A3,
    ROW(Drange)-ROW(INDEX(Drange,1,1))+1),COUNTIF(Drange,A3)-1)))

Note that the last formula is sensitive to empty cells in Erange corresponding to the condition value. If Erange indeed might contain empty or blanl cells, we need to tweak it.

 

[proficient]

Aladin... Awesome...

 

[ls23]

Hi Aladin,

Many thanks for your help so far - truly awesome! I am a bit unfamiliar though how do define Lrow, define Drange and define Erange. Does this involve creating a macro to define these, or is defining them done somewhere in excel? Apologies for the stupid question in advance.

Also, am I right in assuming that if I wanted the third last value, I would subtract 2 from countif, subtract 3 if I wnated the fourth last value etc?

Thanks in advance - just wanted to make sure that I fully grasp the formulas.

 

[Aladin Akyurek]

Hi Aladin,

Many thanks for your help so far - truly awesome! I am a bit unfamiliar though how do define Lrow, define Drange and define Erange. Does this involve creating a macro to define these, or is defining them done somewhere in excel? Apologies for the stupid question in advance.

If you are on a pre-2007 Excel system, you need to activate the option sequence: Insert | Name | Define.

If you are on 2007 system or later, you need to activate the option sequence: Formulas | Name Manager | New.

By the way, when you set the scope for Lrow to the worksheet name ALL, the definition formulas must include ALL!Lrow, not just Lrow as I accidentally jotted down:

Drange:

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

Erange:

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

Also, am I right in assuming that if I wanted the third last value, I would subtract 2 from countif, subtract 3 if I wnated the fourth last value etc?

Yes, the assumption is correct.

Thanks in advance - just wanted to make sure that I fully grasp the formulas.

You are welcome. Recall the caveat though that the formula for the second last value is insensitive to empty cells, associated with the relevant condition. Try to experiment with the formulas in order to see how they behave.