VLOOKUP and LARGE functions

[Efficient]

I am trying to incorporate a function into my spreadsheet that will loo
at all of the sales transactions for a customer and give me the date o
the last transaction without sorting my database.

Attached is a basic worksheet that I can trying to produce the righ
answer utilizing VLOOKUP.

Could anyone please offer suggestions to get the correct answer

Attachment filename: last date.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=47292

 

[jeff]

E:

I just stuck =MAX(B6:B11) in and it retrieved
the max date. You can play with it within
your lookup or perhaps another function to
check the database name.

jeff

 

[Stephen]

Hi

Try this formula
=INDEX(LARGE(B6:B11;1);1;1)

This this get the highst date in the index.

Stephe

 

[JanetW]

If your database contains information for more than one customer, th
formulas presented so far will not work. They will only give you lat
latest transaction for ALL of your customers. If you only want to fin
thelatest transaction for one particular customer, use:

{=MAX(IF(A6:A11="Acme",B6:B11,""))}

If you've never used array formulas (indicated by the curly braces)
here is how you enter them. First enter the formula:

=MAX(IF(A6:A11="Acme",B6:B11,""))

Then, with the cursor in the formula bar, hit "ctrl-shift-enter" whic
will make it an array formula and add the braces

 

[Ken Wright]

Another way without the need to array enter:-

=SUMPRODUCT(MAX((A6:A11="Acme")*(B6:B11)))


Array formula with one less function:-

=MAX((A6:A11="Acme")*(B6:B11))