Vlook to get the latest date

[abhivick]

Hi mates. I'm working on a file where data is entered date wise and I need to keep a track of the latest date entered besides the last order received. When I'm using Vlook up, it shows the date which is on the top of beside that wendor's name but not the latest. As the latest date is mentioned somewhere in the bottom of that column. Please help

 

[BlueHornet]

Welcome to the board.

I don't believe you can get what you want via VLOOKUP, but there is a way that you can get it elsewise. Take a look:

Book1
	A	B	C	D	E	F
1	Account				Account	Call Date
2	A123				A123	1/21/2017 18:01
3	B234				A123	1/21/2017 18:02
4	C345				B222	1/21/2017 18:03
5	AA12				B234	1/21/2017 18:04
6					B234	1/21/2017 18:05
7					C234	1/21/2017 18:06
8					C345	1/21/2017 18:07
9					C345	1/21/2017 18:08
10					C345	1/21/2017 18:09
11					AA11	1/21/2017 18:10
12					AA10	1/21/2017 18:11
13					AA12	1/21/2017 18:12
14					AA12	1/21/2017 18:13
15					AA12	1/21/2017 18:14
16
17
18	Lookup Account
19	B234	2/14/2134 12:10
Sheet1

Cell Formulas
Range	Formula
B19	=SUMPRODUCT((E2:E15 = A19) * (F2:F15))

 

[Tetra201]

Assuming your vendor's names are in range A2:A1000, your dates are in range B2:B1000, and your lookup name is "Vendor01",
try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=MAX((A2:A1000="Vendor01")*B2:B1000)

 

[FDibbins]

See if this will help?
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Name[/td][td]date[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]aa[/td][td]

1/1/2017​

[/td][td][/td][td]aa[/td][td]

1/6/2017​

[/td][/tr]

[tr][td]

3​

[/td][td]bb[/td][td]

1/2/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]cc[/td][td]

1/3/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]dd[/td][td]

1/4/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]ee[/td][td]

1/5/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]aa[/td][td]

1/6/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]bb[/td][td]

1/7/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]cc[/td][td]

1/8/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]dd[/td][td]

1/9/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]ee[/td][td]

1/10/2017​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]
D2 would be your search criteria
E2=MAX(IF($A$2:$A$11=D2,$B$2:$B$11))
this is an ARRAY formula, entered using CTRL SHIFT ENTER, not just enter

edit: Tetra, is there an echo in here...echo in here...n here...LOL

 

[BlueHornet]

Whoops. Yeah, I had been fighting an "unresponsive" worksheet and didn't realize that the recovered version was not the one I wanted when I pasted this. I think we're all working along the same lines though.

Book1
	A	B	C	D	E	F
1	Account				Account	Call Date
2	A123				A123	1/21/2017 18:01
3	B234				A123	1/21/2017 18:02
4	C345				B222	1/21/2017 18:03
5	AA12				B234	1/21/2017 18:04
6					B234	1/21/2017 18:05
7					C234	1/21/2017 18:06
8					C345	1/21/2017 18:07
9					C345	1/21/2017 18:08
10					C345	1/21/2017 18:09
11					AA11	1/21/2017 18:10
12					AA10	1/21/2017 18:11
13					AA12	1/21/2017 18:12
14					AA12	1/21/2017 18:13
15					AA12	1/21/2017 18:14
16
17
18	Lookup Account
19	B234	1/21/2017 18:05
Sheet1

Cell Formulas
Range	Formula
B19	=SUMPRODUCT(MAX((E2:E15 = A19) * (F2:F15)))

 

[Guru008]

Hi,

Try this
=INDEX(F1:G14,MATCH(MAX(G1:G14),G1:G14),1)

I am able to respond in real time to you until it is solved!

 

[FDibbins]

I am able to respond in real time to you until it is solved!

as are pretty much all who answer regularly here, so why bother with that line?