# Last Date Look Up

#### wasatchgirl

##### New Member
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>

### 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
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
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

##### New Member
Thank you! Worked perfectly!

Replies
4
Views
56
Replies
11
Views
87
Replies
1
Views
93
Replies
1
Views
67
Replies
2
Views
41