Excel Query

[willsboy]

Hi,


I have an excel query.


Example -


Vendor Date

123778 22-Jun-11

123778 21-Oct-11

123778 17-Nov-11

123778 11-Jan-12

123778 19-Mar-12


I want the latest date against this vendor if I vlookup. What formula i need to write.


Regards,

Diwakar

 

[Faseeh]

Hi Willsboy,


Welcome to the forums!


With your data in A1:B5, you can use following:

=MAX(IF((A1:A5)=A1,B1:B5))

Press Ctrl+Shift+Enter Format cell as date.


Regards,

 

[willsboy]

thanks...

 

[SirJB7]

Hi, willsboy!

An alternative with normal formulas (not array ones) but with a volatile function as well (SUMPRODUCT):

=INDICE(A2:B6;SUMAPRODUCTO((A2:A6=A2)*(B2:B6=MAX(B2:B6))*FILA(B2:B6))-1;2) -----> in english: =INDEX(A2:B6,SUMPRODUCT((A2:A6=A2)*(B2:B6=MAX(B2:B6))*ROW(B2:B6))-1,2)

Regards!