How can I nest MAX function inside a VLOOKUP?

  • Thread starter Thread starter BrightRed
  • Start date Start date
B

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.
 
Tell us how your data is setup. Where are the vendors and where are the
dates related to the vendors?
 
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.
 
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
 
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
 
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.
 
{=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.
 
Now that works like a charm! Thank you so much!!

Mike H said:
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.
Click to expand...
 
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.
 
Back
Top