# Using VLOOKUP to find date of most current item

#### toongal12

##### Board Regular
So I need to filter my vlookup to find based on a certain number, the most current date compared to a specified date.

Example (my data comes from a pivot table)

A B C
Item 1 10/1/16 12
Item 1 11/1/16 15
Item 1 12/1/16 11
Item 2 10/2/16 14
Item 2 11/2/16 16
Item 2 12/2/16 18

Right now my VLOOKUP will read the first line of a specified item, and return the values from the requested column.

I want to be able to enter the date 11/25/16 for Item 1, and it will return Line 2, because Line 3 has not happened yet.

I figured there would be a filter that would go from highest to lowest date compared to a certain date and return the closest completed value. I am not sure what to do.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Also, this is a long list of items. The dates nor items are in any order. They are jumbled up.

Are your items and dates in separate columns? If so, you can use the following array formula. The data can be in any order.

={MAX(IF((C2:C10<=F2)*(B2:B10=E2),A2:A10))}

Since this is an array formula, after entering formula, highlight cell and press F2, then press CTRL + SHIFT + ENTER. This will enclose the formula in the curly brackets (DO NOT ENTER THE CURLY BRACKETS MANUALLY).

All items and dates are in different columns. A = Item, B = dates, C = value I want returned.

What is E and F referring to? Are these both the date I want to be compared? Why is F being compared to the item I want returned? o:

I also want to do this for every item. So it would output for Item 1 and Item 2 for the date specified.

I also want to do this for every item. So it would output for Item 1 and Item 2 for the date specified.

To put shanesuzanne's idea into perspective:

Excel 2010
ABCDEFGH
1ItemDateNumberDateItemNumber
2Item 110/1/20161211/25/2016Item 115
3Item 111/1/201615Item 216
4Item 112/1/201611
5Item 210/2/201614
6Item 211/2/201616
7Item 212/2/201618
Sheet2
Cell Formulas
RangeFormula
H2{=MAX(IF((\$A\$2:\$A\$7=G2)*(\$B\$2:\$B\$7<=\$E\$2),\$C\$2:\$C\$7))}
Press CTRL+SHIFT+ENTER to enter array formulas.

All items and dates are in different columns. A = Item, B = dates, C = value I want returned.

What is E and F referring to? Are these both the date I want to be compared? Why is F being compared to the item I want returned? o:

Did the picture link work? Today is my first time responding to posts, and I thought that would help to show the cell relationships. I also have an excel file that I can give you the link to if that helps any.

To put shanesuzanne's idea into perspective:

Excel 2010
ABCDEFGH
1ItemDateNumberDateItemNumber
2Item 110/1/20161211/25/2016Item 115
3Item 111/1/201615Item 216
4Item 112/1/201611
5Item 210/2/201614
6Item 211/2/201616
7Item 212/2/201618

</tbody>
Sheet2

Array Formulas
CellFormula
H2{=MAX(IF((\$A\$2:\$A\$7=G2)*(\$B\$2:\$B\$7<=\$E\$2),\$C\$2:\$C\$7))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

When I try that, I get 0 for all answers

\$R\$7 holds my date. A21 begins my static table of Item numbers 1-10.

I'm grabbing data from a pivot table. So I try =MAX(IF((ItemData[ItemName]=A21)*(ItemData[FinishedDate]<=\$R\$7),ItemData[ItemSerial])) with the Ctrl+Shift+Enter, and only zeros return

Are you trying to return a number field (like column C in the screen print above)? It doesn't seem to work if the cell you are telling it to return has text in it.

Are you trying to return a number field (like column C in the screen print above)? It doesn't seem to work if the cell you are telling it to return has text in it.

I want to be able to output numbers and text. It does not output either for me.

Replies
3
Views
376
Replies
9
Views
2K
Replies
1
Views
952
Replies
0
Views
490
Replies
2
Views
263

1,203,515
Messages
6,055,850
Members
444,828
Latest member
StaffordStag

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back