Forum Discussion

Copper Contributor

Jul 22, 2025

Best way to use maxifs combined with replacing values in "lookup array"

Hi, I am trying to find the best way to create a simple yet interesting formula in excel I have 4 arrays of values all the same size. The lookup array, a "replacement value array" and 2 arrays...

excel

Formulas and Functions

Ozzy7

Copper Contributor

Jul 24, 2025

I like this solution except it isn't working for me for some reason. I think it is breaking down when giving the conditional statement to the 1st variable name

=LET(
summarised,IF('150m_Results'!U5:U1084 < 0.1,'150m_Results'!BC5:BC1084,'150m_Results'!U5:U1084),
WaveHcrit,'150m_Results'!C5:C1084,WavePcrit,'150m_Results'!D5:D1084,WaveDircrit,'150m_Results'!E5:E1084,
MAXIFS(summarised,WaveHcrit,C5,WavePcrit,D5,WaveDircrit,">15"))

All references 1 x 1080 arrays except "C5" and "D5" used in the maxifs function at the end

Patrick2788

Silver Contributor

Jul 24, 2025

Check Peter's arrangement again. MAXIFS won't work because it will only accept ranges. You must use MAX(IF( arrangement.

m_tarler

Bronze Contributor

Jul 24, 2025

alternatively just combine the conditionals:

=MAX(
IF('150m_Results'!U5:U1084 < 0.1,'150m_Results'!BC5:BC1084,'150m_Results'!U5:U1084)
*('150m_Results'!C5:C1084=C5)
*('150m_Results'!D5:D1084=D5)
*('150m_Results'!E5:E1084>15)
)