How can I nest MAX function inside a VLOOKUP?

[BrightRed]

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.

 

[T. Valko]

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

 

[Mike H]

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.

 

[Paul C]

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

 

[BrightRed]

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]

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.

 

[T. Valko]

{=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.

 

[BrightRed]

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

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.

 

[Mike H]

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.