Last Date Look Up

[wasatchgirl]

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!

	A	B	C	D
1	DATE	COLOR	TARGET	LAST APPEARANCE
2	9/1/2014	Red	Yellow	9/17/2014
3	9/2/2014	Yellow	Green	9/18/2014
4	9/3/2014	Green	Blue	9/19/2014
5	9/4/2014	Blue
6	9/5/2014	Black
7	9/6/2014	Red
8	9/7/2014	Yellow
9	9/8/2014	Green
10	9/9/2014	Blue
11	9/10/2014	Black
12	9/11/2014	Red
13	9/12/2014	Yellow
14	9/13/2014	Green
15	9/14/2014	Blue
16	9/15/2014	Black
17	9/16/2014	Red
18	9/17/2014	Yellow
19	9/18/2014	Green
20	9/19/2014	Blue
21	9/20/2014	Black

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

 

[NeonRedSharpie]

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:

=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.

 

[Scott Huish]

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>

 

[wasatchgirl]

Thank you! Worked perfectly!