Retrieve data with MAX and VLOOKUP
 

I have a data table I'm trying to retrieve data from(to another worksheet)
that gives me the highest number in a list for a specific ID and only if the
#days is between 15 and 30.

For example:

Data table:
A B C

ID Type #days
80 MD 15
62 MD 22
28 MD 10
80 MD 28
62 MD 1

Would like to lookup the ID and give me the highest # days in the list for
those
with #days between 15 and 30.

So results for this would be:

ID Type #days
80 MD 28
62 MD 22

Can anyone help?

Retrieve data with MAX and VLOOKUP
 

Tasha wrote:
I have a data table I'm trying to retrieve data from(to another worksheet)
that gives me the highest number in a list for a specific ID and only if the
#days is between 15 and 30.

For example:

Data table:
A B C

ID Type #days
80 MD 15
62 MD 22
28 MD 10
80 MD 28
62 MD 1

Would like to lookup the ID and give me the highest # days in the list for
those
with #days between 15 and 30.

So results for this would be:

ID Type #days
80 MD 28
62 MD 22

Can anyone help?



Supposing you have an ID to test in E2, this array* formula will do it:

=MAX(IF(($A$2:$A$6=E2)*($C$2:$C$6=15)*($C$2:$C$6< =30),C$2:$C$6))

*Commit the array formula by pressing Ctrl+Shift+Enter. Do not just
press Enter or Tab.

Retrieve data with MAX and VLOOKUP
 

worked perfectly THANK YOU!!!

"smartin" wrote:

Tasha wrote:
I have a data table I'm trying to retrieve data from(to another worksheet)
that gives me the highest number in a list for a specific ID and only if the
#days is between 15 and 30.

For example:

Data table:
A B C

ID Type #days
80 MD 15
62 MD 22
28 MD 10
80 MD 28
62 MD 1

Would like to lookup the ID and give me the highest # days in the list for
those
with #days between 15 and 30.

So results for this would be:

ID Type #days
80 MD 28
62 MD 22

Can anyone help?



Supposing you have an ID to test in E2, this array* formula will do it:

=MAX(IF(($A$2:$A$6=E2)*($C$2:$C$6=15)*($C$2:$C$6< =30),C$2:$C$6))

*Commit the array formula by pressing Ctrl+Shift+Enter. Do not just
press Enter or Tab.