Find and return the highest number and corresponding date based on a condition
This article describes how to filter records based on the maximum value of a specific item. There are names in column B, dates in column C, and numbers in column D.
The formula in cell G3 extracts numbers from column D based on the given condition in cell F3. The highest number is then calculated and returned to cell G3 with the corresponding date in column H.
Denis asks:
thank you for sharing you knowledge and helping us with these excellent formulas.
I have a case i could really need your help with:
The following Table shows a history of names, stati and the date the status was acquired. BUT: people can acquire the same status more than just once (or at least report it). Now I want to know, when each person (peter, sarah & luke) have acquired their individual highest status.Name Date Status
peter 30.01.2015 5
sarah 30.01.2015 5
peter 28.01.2015 5
sarah 28.01.2015 4
peter 24.01.2015 5
peter 22.01.2015 5
sarah 22.01.2015 3
luke 22.01.2015 4
peter 20.01.2015 3
sarah 20.01.2015 3
sarah 18.01.2015 2
peter 18.01.2015 2
luke 18.01.2015 3
luke 16.01.2015 3
luke 14.01.2015 2
peter 14.01.2015 2
peter 12.01.2015 1
sarah 12.01.2015 2
peter 10.01.2015 1
sarah 10.01.2015 2
sarah 08.01.2015 1answers have to be :
Names latest status in status since
Peter 5 22.01.2015
sarah 5 30.01.2015
luke 4 22.01.2015I would really appreciate your help.
Greetings.
Answer:
Array formula in G3:
Array formula in cell H3:
How to enter an array formula
- Select cell F2.
- Paste formula in formula bar.
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
- Release all keys.
This is what the formula in the formula bar looks like:
Don't enter the curly brackets yourself, they appear automatically when you press CTRL + SHIFT + ENTER, see steps above.
Update 2021-05-20, The MAXIFS and MINIFS functions were introduced in Excel 2016. Enter this formula as regular formula in cell G3:
Read more: MAXIFS function
Excel 2016 formula in cell H3:
Read more: MINIFS function
These two formulas are regular formulas.
Explaining array formula in cell G3
Step 1 - Find name in cell E2 in cell range B2:B22
The equal sign lets you compare a value to another value, you can also compare a value to an entire array of values. This is what we are going to do here.
The equal sign is one of many logical operators and the result is a boolean value TRUE or FALSE, or their numerical equivalents 1 (TRUE) or 0 (FALSE).
F3=$B$3:$B$23
becomes
"peter"={"peter"; "sarah"; "peter"; "sarah"; "peter"; "peter"; "sarah"; "luke"; "peter"; "sarah"; "sarah"; "peter"; "luke"; "luke"; "luke"; "peter"; "peter"; "sarah"; "peter"; "sarah"; "sarah"}
and returns
{TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE}
Step 2 - Replace TRUE with corresponding value in cell range C2:C22 and FALSE with nothing
The IF function returns one value if the logical test is TRUE and another value if FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(F3=$B$3:$B$23, $D$3:$D$23, "")
becomes
IF({TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE}, $C$2:$C$22, "")
becomes
IF({TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE}, {5; 5; 5; 4; 5; 5; 3; 4; 3; 3; 2; 2; 3; 3; 2; 2; 1; 2; 1; 2; 1}, "")
and returns
{5; ""; 5; ""; 5; 5; ""; ""; 3; ""; ""; 2; ""; ""; ""; 2; 1; ""; 1; ""; ""}
Step 3 - Find largest value in array
The MAX function returns the largest number froma cell range or an array.
MAX(IF(F3=$B$3:$B$23, $D$3:$D$23,""))
becomes
MAX({5; ""; 5; ""; 5; 5; ""; ""; 3; ""; ""; 2; ""; ""; ""; 2; 1; ""; 1; ""; ""})
and returns 5 in cell F2.
Explaining array formula in cell H3
Step 1 - Compare largest value with cell range $D$3:$D$23
This step returns an array containing boolean values, TRUE if they meet the condition and FALSE if not.
MAX(IF(F3=$B$3:$B$23, $D$3:$D$23, ""))=$D$3:$D$23
becomes
5=$D$3:$D$23
and returns
{TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Step 2 - Compare value in cell F3 with cell range $B$3:$B$23
This step compares the name in cell F3 to the values in cell range $B$3:$B$23.
F3=$B$3:$B$23
returns
{TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE}
Step 3 - Multiply array in step 1 with array in step 2
This step applies AND logic meaning both conditions must be met in order to return TRUE.
TRUE * TRUE = TRUE (1)
TRUE * FALSE = FALSE (0)
FALSE * TRUE = FALSE (0)
FALSE * FALSE = FALSE (0)
When you multiply boolean values you always get the numerical equivalent, TRUE = 1 and FALSE = 0 (zero)
MIN(IF((MAX(IF(F3=$B$3:$B$23, $D$3:$D$23, ""))=$D$3:$D$23)*(F3=$B$3:$B$23),$C$3:$C$23, "A"))
MAX(IF(E2=$A$2:$A$22,$C$2:$C$22,""))=$C$2:$C$22)*(E2=$A$2:$A$22)
becomes
{TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE} *Â {TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE}
and returns
{1; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Step 4 - Replace TRUE with corresponding value in cell range C2:C22 and FALSE with random text string
The IF function returns one value if the logical test is TRUE and another value if FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(E2=$A$2:$A$22,$C$2:$C$22,""))=$C$2:$C$22)*(E2=$A$2:$A$22),$B$2:$B$22,"A")
bcomes
IF({1; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0},$B$2:$B$22,"A")
and returns
{42034; "A"; 42032; "A"; 42028; 42026; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"}
Step 5 - Find smallest value in array, text strings are ignored
The MIN function returns the smallest number in a cell range or array, text strings are ignored.
MIN(IF((MAX(IF(E2=$A$2:$A$22,$C$2:$C$22,""))=$C$2:$C$22)*(E2=$A$2:$A$22),$B$2:$B$22,"A"))
becomes
MIN({42034; "A"; 42032; "A"; 42028; 42026; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"})
and returns 42026 in cell G2.
Functions in this article
More than 1300 Excel formulas
Excel categories
One Response to “Find and return the highest number and corresponding date based on a condition”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Just because I've noticed this
"Step 2 - Compare value in cell E2 with cell range A2:A2"
you miss the last 2 on A22 range
thanks for all your advice, I discover your site today and I felt in love