Finding MAX value with criteria list
Solved/Closed
RayH
fdibbins
- Posts
- 122
- Registration date
- Tuesday August 31, 2010
- Status
- Contributor
- Last seen
- June 20, 2016
fdibbins
- Posts
- 33
- Registration date
- Sunday June 19, 2016
- Status
- Contributor
- Last seen
- November 20, 2016
Related:
- Finding MAX value with criteria list
- How to make your best friends list on snapchat smaller - Guide
- Little alchemy list - Guide
- Samsung guru block list - Guide
- List installation steps of jsdk - How-To - Webmaster
- If cell contains (multiple text criteria) then return (corresponding text criteria) ✓ - Forum - Excel
5 replies
vcoolio
Jun 19, 2016 at 04:00 AM
- Posts
- 1347
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- June 10, 2022
Jun 19, 2016 at 04:00 AM
Hey Ray,
I'm just about to head off into the sunset but I was thinking that perhaps you could use the INDEX/MATCH function wrapped in the MAX function. Something like:-
=MAX(INDEX($C$1:$C$8,MATCH(A6,$B$1:B$8,0)))
assuming the values are in Columns A, B, & C.
If there isn't a matching value, it should return N/A in the column in which you place the formula. But I'm not sure if it could handle and give the right return if there is a value in the first column with two matches in the second column (such as the value "M").
Have a fiddle with it. You never know.
Formulae are not a strong point with me so where is Mazzaropi when you need him?!!?
I'm off. See ya.
Cheerio,
vcoolio.
I'm just about to head off into the sunset but I was thinking that perhaps you could use the INDEX/MATCH function wrapped in the MAX function. Something like:-
=MAX(INDEX($C$1:$C$8,MATCH(A6,$B$1:B$8,0)))
assuming the values are in Columns A, B, & C.
If there isn't a matching value, it should return N/A in the column in which you place the formula. But I'm not sure if it could handle and give the right return if there is a value in the first column with two matches in the second column (such as the value "M").
Have a fiddle with it. You never know.
Formulae are not a strong point with me so where is Mazzaropi when you need him?!!?
I'm off. See ya.
Cheerio,
vcoolio.
fdibbins
Jun 19, 2016 at 11:39 PM
- Posts
- 33
- Registration date
- Sunday June 19, 2016
- Status
- Contributor
- Last seen
- November 20, 2016
Jun 19, 2016 at 11:39 PM
=(MAX(IF(C2:C111=ValueOnStatusTable,S2:S200)))
that is an ARRAY formula and needs to be entered using CTRL SHIFT ENTER, not just enter
=MAX(INDEX($C$1:$C$8,MATCH(A6,$B$1:B$8,0)))
Dont think this will work. INDEX/MATCH will return 1 value, so there will be nothing for MAX to work on.
The MAX/IF should work though
that is an ARRAY formula and needs to be entered using CTRL SHIFT ENTER, not just enter
=MAX(INDEX($C$1:$C$8,MATCH(A6,$B$1:B$8,0)))
Dont think this will work. INDEX/MATCH will return 1 value, so there will be nothing for MAX to work on.
The MAX/IF should work though
RayH
Jun 19, 2016 at 11:48 PM
- Posts
- 122
- Registration date
- Tuesday August 31, 2010
- Status
- Contributor
- Last seen
- June 20, 2016
Jun 19, 2016 at 11:48 PM
Hi fdibbins,
Yeah, I tried that too.
What I would I put in place of ValueOnStatusTable though? Using the range of the table I get #N/A indicating that it cant find a value??
Both the Status column (C) and the ValidStatus range are formatted as Text. Sometimes this causes a problem when they are different.
Yeah, I tried that too.
What I would I put in place of ValueOnStatusTable though? Using the range of the table I get #N/A indicating that it cant find a value??
Both the Status column (C) and the ValidStatus range are formatted as Text. Sometimes this causes a problem when they are different.
fdibbins
Jun 20, 2016 at 02:10 AM
- Posts
- 33
- Registration date
- Sunday June 19, 2016
- Status
- Contributor
- Last seen
- November 20, 2016
Jun 20, 2016 at 02:10 AM
If the range you are trying to find the max of, is formatted as text, that is what is causing the problem - you cannot find the max of text...which is greater, blue or green?
You will need to convert those text "values" back to numbers 1st
hmm or maybe try using SUMPRODUCT()
You will need to convert those text "values" back to numbers 1st
hmm or maybe try using SUMPRODUCT()
RayH
Jun 20, 2016 at 11:01 AM
- Posts
- 122
- Registration date
- Tuesday August 31, 2010
- Status
- Contributor
- Last seen
- June 20, 2016
Jun 20, 2016 at 11:01 AM
"Both the Status column (C) and the ValidStatus range are formatted as Text. "
The value I am trying to MAX is formatted as "General"
Given this scenario, how do I pick out those values to SUMPRODUCT with?
The value I am trying to MAX is formatted as "General"
Given this scenario, how do I pick out those values to SUMPRODUCT with?
vcoolio
Jun 20, 2016 at 03:18 AM
- Posts
- 1347
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- June 10, 2022
Jun 20, 2016 at 03:18 AM
Hi Ford,
Welcome to CCM. Glad to see you here.
I see your logic as I'm sure that Ray can. It is a tricky one though!
Over to Ray...........
Thanks Ford.
Cheerio,
vcoolio.
Welcome to CCM. Glad to see you here.
I see your logic as I'm sure that Ray can. It is a tricky one though!
Over to Ray...........
Thanks Ford.
Cheerio,
vcoolio.
fdibbins
Jun 20, 2016 at 11:14 PM
- Posts
- 33
- Registration date
- Sunday June 19, 2016
- Status
- Contributor
- Last seen
- November 20, 2016
Jun 20, 2016 at 11:14 PM
Thanks for the invite, hope I can be of help here :)
Didn't find the answer you are looking for?
Ask a question
TrowaD
Jun 20, 2016 at 11:20 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Jun 20, 2016 at 11:20 AM
Hey guys,
Try reserving an extra column for this formula:
=IF(ISERROR(VLOOKUP(C2,$A$2:$A$6,1,0)),0,1)
I placed this in column T, but you can use any column, which you can then hide or place out of sight.
Then use this formula to get your answer:
=MAX((T2:T9=1)*S2:S9)
This is an array formula; confirm with Ctrl+Shift+Enter.
Best regards,
Trowa
Try reserving an extra column for this formula:
=IF(ISERROR(VLOOKUP(C2,$A$2:$A$6,1,0)),0,1)
I placed this in column T, but you can use any column, which you can then hide or place out of sight.
Then use this formula to get your answer:
=MAX((T2:T9=1)*S2:S9)
This is an array formula; confirm with Ctrl+Shift+Enter.
Best regards,
Trowa
RayH
Jun 20, 2016 at 11:31 AM
- Posts
- 122
- Registration date
- Tuesday August 31, 2010
- Status
- Contributor
- Last seen
- June 20, 2016
Jun 20, 2016 at 11:31 AM
Thanks TrowaD
That works. I was hoping not to use a helper column though but I'll take what I can get at this stage.
That works. I was hoping not to use a helper column though but I'll take what I can get at this stage.
RayH
Jun 20, 2016 at 01:23 PM
- Posts
- 122
- Registration date
- Tuesday August 31, 2010
- Status
- Contributor
- Last seen
- June 20, 2016
Jun 20, 2016 at 01:23 PM
Those wanting to use this for MIN you have to wrap this in an IF to check for ZERO
=MIN(IF(($T2:$T9=1)*$S2:$S9>0,$S2:$S9))
Jun 19, 2016 at 10:52 AM
Thanks for the reply.
I think the solution you gave was one of the many variants I tried already.
It does return a value but doesn't take the whole valid status table into account and returns just the first instance of the status.
Bear in mind that it should return the MAX value of all of those statuses not individually.
I'd prefer a Maserati but I'd settle for Mazzaropi right now.