Using VLOOKUP to find date of most current item

[toongal12]

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.

 

[toongal12]

Also, this is a long list of items. The dates nor items are in any order. They are jumbled up.

 

[shanesuzanne]

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

 

[toongal12]

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:

 

[toongal12]

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

 

[63falcondude]

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
	A	B	C	D	E	F	G	H
1	Item	Date	Number		Date		Item	Number
2	Item 1	10/1/2016	12		11/25/2016		Item 1	15
3	Item 1	11/1/2016	15				Item 2	16
4	Item 1	12/1/2016	11
5	Item 2	10/2/2016	14
6	Item 2	11/2/2016	16
7	Item 2	12/2/2016	18
Sheet2

Cell Formulas
Range	Formula
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.

 

[shanesuzanne]

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.

 

[toongal12]

To put shanesuzanne's idea into perspective:

Excel 2010

	A	B	C	D	E	F	G	H
1	Item	Date	Number		Date		Item	Number
2	Item 1	10/1/2016	12		11/25/2016		Item 1	15
3	Item 1	11/1/2016	15				Item 2	16
4	Item 1	12/1/2016	11
5	Item 2	10/2/2016	14
6	Item 2	11/2/2016	16
7	Item 2	12/2/2016	18

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

Sheet2​

Array Formulas Cell Formula H2 {=MAX(IF(($A$2:$A$7=G2)*($B$2:$B$7<=$E$2),$C$2:$C$7))} <thead> </thead><tbody> </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

 

[shanesuzanne]

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.

 

[toongal12]

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.