Last Date Look Up

wasatchgirl

New Member
Joined
Feb 3, 2014
Messages
5
I'm sure this has been asked before, I've searched everywhere and can't find it. I am so struggling to create a formula that will look up a specific variable in a list of variables and then look for the latest occurrence of that variable and return that date. For example, if the range of variables and dates are listed below in A1:B21 and the specific variable is "Yellow" (C2) then the formula in D2 would return 9/17/2014 (the last occurrence of the target variable). Any help would be so very much appreciated! Thanks guys!

ABCD
1DATECOLORTARGETLAST APPEARANCE
29/1/2014RedYellow9/17/2014
39/2/2014YellowGreen9/18/2014
49/3/2014GreenBlue9/19/2014
59/4/2014Blue
69/5/2014Black
79/6/2014Red
89/7/2014Yellow
99/8/2014Green
109/9/2014Blue
119/10/2014Black
129/11/2014Red
139/12/2014Yellow
149/13/2014Green
159/14/2014Blue
169/15/2014Black
179/16/2014Red
189/17/2014Yellow
199/18/2014Green
209/19/2014Blue
219/20/2014Black

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
Function MAXIF(rng As Range, nCell As Range, val As Range) As Variant

    Dim arr As Variant
    arr = val


    For Each cell In rng
        If cell.Value = nCell.Value Then
            If val(cell.Row) > MAXIF Then MAXIF = val(cell.Row)
        End If
    Next cell
End Function

I don't know if you want a UDF, but if you do here is one. The input would look like:

Code:
=maxif($A$1:$A$10,$A1,$B$1:$B$10)

I wrote this a while ago but it's relevant.




ETA: Tried it on your sample data and I can't get the right results...strange. Investigation time.
 
Last edited:

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
IN D2:
=LOOKUP(2,1/($B$2:$B$21=C2),$A$2:$A$21)

Copy down.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:115px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:116px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:55px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >DATE</td><td >COLOR</td><td >TARGET</td><td >LAST APPEARANCE</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">9/1/2014</td><td >Red</td><td >Yellow</td><td style="text-align:right; ">9/17/2014</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">9/2/2014</td><td >Yellow</td><td >Green</td><td style="text-align:right; ">9/18/2014</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">9/3/2014</td><td >Green</td><td >Blue</td><td style="text-align:right; ">9/19/2014</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">9/4/2014</td><td >Blue</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">9/5/2014</td><td >Black</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">9/6/2014</td><td >Red</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">9/7/2014</td><td >Yellow</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">9/8/2014</td><td >Green</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">9/9/2014</td><td >Blue</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">9/10/2014</td><td >Black</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">9/11/2014</td><td >Red</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">9/12/2014</td><td >Yellow</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">9/13/2014</td><td >Green</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">9/14/2014</td><td >Blue</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">9/15/2014</td><td >Black</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">9/16/2014</td><td >Red</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">9/17/2014</td><td >Yellow</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">9/18/2014</td><td >Green</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">9/19/2014</td><td >Blue</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">9/20/2014</td><td >Black</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=LOOKUP(2,1/<span style=' color:008000; '>($B$2:$B$21=C2)</span>,$A$2:$A$21)</td></tr><tr><td >D3</td><td >=LOOKUP(2,1/<span style=' color:008000; '>($B$2:$B$21=C3)</span>,$A$2:$A$21)</td></tr><tr><td >D4</td><td >=LOOKUP(2,1/<span style=' color:008000; '>($B$2:$B$21=C4)</span>,$A$2:$A$21)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>
 

Watch MrExcel Video

Forum statistics

Threads
1,109,329
Messages
5,528,033
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top