Hi,
I have a list of vendors, each having done work on several different dates.
I need to find each vendor's latest worked date and display them on a
separate worksheet.

Is using a MAX inside VLOOKUP the right approach to this problem?

Can this be done without adding additional columns?

Please advice and thanks for you help.

Tell us how your data is setup. Where are the vendors and where are the
dates related to the vendors?

--
Biff
Microsoft Excel MVP


"BrightRed" wrote in message
...
Hi,
I have a list of vendors, each having done work on several different
dates.
I need to find each vendor's latest worked date and display them on a
separate worksheet.

Is using a MAX inside VLOOKUP the right approach to this problem?

Can this be done without adding additional columns?

Please advice and thanks for you help.

Hi,

You should always provide information on your data layout to avoid the need
for us to guess. This assumes vendor in column A and dates in column B. This
is an ARRAY formula.

Vendor being looked up in C1

=MAX(IF(A1:A100=C1,B1:B100))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"BrightRed" wrote:

Hi,
I have a list of vendors, each having done work on several different dates.
I need to find each vendor's latest worked date and display them on a
separate worksheet.

Is using a MAX inside VLOOKUP the right approach to this problem?

Can this be done without adding additional columns?

Please advice and thanks for you help.

The VLOOKUP wont work properly unless the data is sorted first and it and it
can still be tricky to find the correct match.

What you need is a array (or CSE) function like this

{=MAXA(--((A2:A7="Vendor1")*B2:B7))}

This assumes the Vendor info is in A2:A7 and the dates in B2:B7

you do not type the {}, type the formula and instead of just hitting enter
you key Ctrl+Shift+Enter (CSE) and the brackets get added. You have to use
the CSE everytime you edit the formula. This can be a bit of a pain but
array functions can be very useful.

The (A2:A7="Vendor1") part evaluates to 0 for all cells in the range that do
not equal Vendor1 and 1 for all cells that do equal Vendor1 and multiples
this by the corresponding date, thus eliminating all non Vendor1 values and
then find the max.

Chip Pearson has a pretty good intro to array functions on his site
http://www.cpearson.com/excel/ArrayFormulas.aspx
--
If this helps, please remember to click yes.


"BrightRed" wrote:

Hi,
I have a list of vendors, each having done work on several different dates.
I need to find each vendor's latest worked date and display them on a
separate worksheet.

Is using a MAX inside VLOOKUP the right approach to this problem?

Can this be done without adding additional columns?

Please advice and thanks for you help.

Sorry, I'm new to this forum and could not find the option to upload a sample
file. But please see the below sample layout of my data:

SHEET 1 shows the vendor numbers (column A) and their corresponding worked
dates (column B).
SHEET 2 is where I need to display the "most recent worked dates" (column B)
for each vendor.

Thanks to everyone for your help!

***** SHEET 1 *****
Vendor Date Worked
------ -----------
1-00004 11/28/2008
1-00004 11/25/2009
1-00005 11/26/2008
1-00005 11/23/2009
1-00007 12/19/2008
1-00010 12/2/2009
1-00013 2/13/2009
1-00013 2/19/2010
1-00014 9/24/2008
1-00014 12/30/2008
1-00014 10/2/2009
1-00014 10/29/2009
1-00015 8/6/2008
1-00015 1/20/2009
1-00016 12/23/2008
1-00016 1/24/2010

***** SHEET 2 *****
Vendor Most Recent Worked Date
------ ----------------------
1-00004
1-00005
1-00006
1-00007
1-00008
1-00010
1-00011
1-00012
1-00013
1-00014
1-00015



"Mike H" wrote:

Hi,

You should always provide information on your data layout to avoid the need
for us to guess. This assumes vendor in column A and dates in column B. This
is an ARRAY formula.

Vendor being looked up in C1

=MAX(IF(A1:A100=C1,B1:B100))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"BrightRed" wrote:

Hi,
I have a list of vendors, each having done work on several different dates.
I need to find each vendor's latest worked date and display them on a
separate worksheet.

Is using a MAX inside VLOOKUP the right approach to this problem?

Can this be done without adding additional columns?

Please advice and thanks for you help.

Hi,

It's the same formula as I gave you entered as an array and dragged down

=MAX(IF(Sheet1!$A$2:$A$100=A2,Sheet1!$B$2:$B$100))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"BrightRed" wrote:

Sorry, I'm new to this forum and could not find the option to upload a sample
file. But please see the below sample layout of my data:

SHEET 1 shows the vendor numbers (column A) and their corresponding worked
dates (column B).
SHEET 2 is where I need to display the "most recent worked dates" (column B)
for each vendor.

Thanks to everyone for your help!

***** SHEET 1 *****
Vendor Date Worked
------ -----------
1-00004 11/28/2008
1-00004 11/25/2009
1-00005 11/26/2008
1-00005 11/23/2009
1-00007 12/19/2008
1-00010 12/2/2009
1-00013 2/13/2009
1-00013 2/19/2010
1-00014 9/24/2008
1-00014 12/30/2008
1-00014 10/2/2009
1-00014 10/29/2009
1-00015 8/6/2008
1-00015 1/20/2009
1-00016 12/23/2008
1-00016 1/24/2010

***** SHEET 2 *****
Vendor Most Recent Worked Date
------ ----------------------
1-00004
1-00005
1-00006
1-00007
1-00008
1-00010
1-00011
1-00012
1-00013
1-00014
1-00015



"Mike H" wrote:

Hi,

You should always provide information on your data layout to avoid the need
for us to guess. This assumes vendor in column A and dates in column B. This
is an ARRAY formula.

Vendor being looked up in C1

=MAX(IF(A1:A100=C1,B1:B100))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"BrightRed" wrote:

Hi,
I have a list of vendors, each having done work on several different dates.
I need to find each vendor's latest worked date and display them on a
separate worksheet.

Is using a MAX inside VLOOKUP the right approach to this problem?

Can this be done without adding additional columns?

Please advice and thanks for you help.

{=MAXA(--((A2:A7="Vendor1")*B2:B7))}

The double unary is not needed in this application.

=MAX((A2:A7="Vendor1")*B2:B7)

For a larger range the MAX(IF version would be a bit more efficient:

=MAX(IF(A2:A1000="Vendor1",B2:B1000))

Both formulas are array entered.

--
Biff
Microsoft Excel MVP


"Paul C" wrote in message
...
The VLOOKUP wont work properly unless the data is sorted first and it and
it
can still be tricky to find the correct match.

What you need is a array (or CSE) function like this

{=MAXA(--((A2:A7="Vendor1")*B2:B7))}

This assumes the Vendor info is in A2:A7 and the dates in B2:B7

you do not type the {}, type the formula and instead of just hitting enter
you key Ctrl+Shift+Enter (CSE) and the brackets get added. You have to
use
the CSE everytime you edit the formula. This can be a bit of a pain but
array functions can be very useful.

The (A2:A7="Vendor1") part evaluates to 0 for all cells in the range that
do
not equal Vendor1 and 1 for all cells that do equal Vendor1 and multiples
this by the corresponding date, thus eliminating all non Vendor1 values
and
then find the max.

Chip Pearson has a pretty good intro to array functions on his site
http://www.cpearson.com/excel/ArrayFormulas.aspx
--
If this helps, please remember to click yes.


"BrightRed" wrote:

Hi,
I have a list of vendors, each having done work on several different
dates.
I need to find each vendor's latest worked date and display them on a
separate worksheet.

Is using a MAX inside VLOOKUP the right approach to this problem?

Can this be done without adding additional columns?

Please advice and thanks for you help.

Now that works like a charm! Thank you so much!!

"Mike H" wrote:

Hi,

It's the same formula as I gave you entered as an array and dragged down

=MAX(IF(Sheet1!$A$2:$A$100=A2,Sheet1!$B$2:$B$100))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"BrightRed" wrote:

Sorry, I'm new to this forum and could not find the option to upload a sample
file. But please see the below sample layout of my data:

SHEET 1 shows the vendor numbers (column A) and their corresponding worked
dates (column B).
SHEET 2 is where I need to display the "most recent worked dates" (column B)
for each vendor.

Thanks to everyone for your help!

***** SHEET 1 *****
Vendor Date Worked
------ -----------
1-00004 11/28/2008
1-00004 11/25/2009
1-00005 11/26/2008
1-00005 11/23/2009
1-00007 12/19/2008
1-00010 12/2/2009
1-00013 2/13/2009
1-00013 2/19/2010
1-00014 9/24/2008
1-00014 12/30/2008
1-00014 10/2/2009
1-00014 10/29/2009
1-00015 8/6/2008
1-00015 1/20/2009
1-00016 12/23/2008
1-00016 1/24/2010

***** SHEET 2 *****
Vendor Most Recent Worked Date
------ ----------------------
1-00004
1-00005
1-00006
1-00007
1-00008
1-00010
1-00011
1-00012
1-00013
1-00014
1-00015



"Mike H" wrote:

Hi,

You should always provide information on your data layout to avoid the need
for us to guess. This assumes vendor in column A and dates in column B. This
is an ARRAY formula.

Vendor being looked up in C1

=MAX(IF(A1:A100=C1,B1:B100))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"BrightRed" wrote:

Hi,
I have a list of vendors, each having done work on several different dates.
I need to find each vendor's latest worked date and display them on a
separate worksheet.

Is using a MAX inside VLOOKUP the right approach to this problem?

Can this be done without adding additional columns?

Please advice and thanks for you help.

Glad I could help and thanks for the feed back
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"BrightRed" wrote:

Now that works like a charm! Thank you so much!!

"Mike H" wrote:

Hi,

It's the same formula as I gave you entered as an array and dragged down

=MAX(IF(Sheet1!$A$2:$A$100=A2,Sheet1!$B$2:$B$100))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"BrightRed" wrote:

Sorry, I'm new to this forum and could not find the option to upload a sample
file. But please see the below sample layout of my data:

SHEET 1 shows the vendor numbers (column A) and their corresponding worked
dates (column B).
SHEET 2 is where I need to display the "most recent worked dates" (column B)
for each vendor.

Thanks to everyone for your help!

***** SHEET 1 *****
Vendor Date Worked
------ -----------
1-00004 11/28/2008
1-00004 11/25/2009
1-00005 11/26/2008
1-00005 11/23/2009
1-00007 12/19/2008
1-00010 12/2/2009
1-00013 2/13/2009
1-00013 2/19/2010
1-00014 9/24/2008
1-00014 12/30/2008
1-00014 10/2/2009
1-00014 10/29/2009
1-00015 8/6/2008
1-00015 1/20/2009
1-00016 12/23/2008
1-00016 1/24/2010

***** SHEET 2 *****
Vendor Most Recent Worked Date
------ ----------------------
1-00004
1-00005
1-00006
1-00007
1-00008
1-00010
1-00011
1-00012
1-00013
1-00014
1-00015



"Mike H" wrote:

Hi,

You should always provide information on your data layout to avoid the need
for us to guess. This assumes vendor in column A and dates in column B. This
is an ARRAY formula.

Vendor being looked up in C1

=MAX(IF(A1:A100=C1,B1:B100))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"BrightRed" wrote:

Hi,
I have a list of vendors, each having done work on several different dates.
I need to find each vendor's latest worked date and display them on a
separate worksheet.

Is using a MAX inside VLOOKUP the right approach to this problem?

Can this be done without adding additional columns?

Please advice and thanks for you help.