Forum Discussion

Ozzy7's avatar
Ozzy7
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 that have values used for criteria.

I want to first replace any values in the lookup array with the corresponding values from the "replacement value array" based on a certain condition such as <0.1.

Then, go through the modified lookup array and find the maximum value in there based on criteria using corresponding values from the 2 other arrays.

 

I can obviously do this quit easily through creating the modified array first and then utilising the maxifs function but I am dealing with massive amounts of arrays and would Ideally like to keep it to one cell so I can summarise nicely in a table without creating extra sheets in my workbook.

 

If I want to do this in one cell, am I better off using the python add in (which I have) and if so, what would be the best way to do that?

 

Cheers,

(haven`t posted in here before so sorry if not the best way to describe or if is a sh!tty problem)

 
excel
Formulas and Functions

6 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jul 23, 2025

    You could very likely us an Excel formula.  It lacks Python's extensive libraries but, other that that, it is perfectly capable of carrying out calculation.

    = LET(
    modifiedArr,    IF(lookupArr<0.1, replacementArr, lookupArr),
    conditionalMax, MAX(IF(criterionArr, modifiedArr)),
    conditionalMax
  )

     

    • Ozzy7's avatar
      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's avatar
        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.

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Jul 23, 2025

    Can you please attach a sample file or share via google-drive or OneDrive so that we can have a look? Show your input data and desired output manually and we will try to automate that output by formula.

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Jul 23, 2025

    Take this when preference on Python:

     

    import pandas as pd
import numpy as np

def max_filtered(lookup, replace, crit1, crit2, crit1_val, crit2_val):
    lookup = np.array(lookup)
    replace = np.array(replace)
    crit1 = np.array(crit1)
    crit2 = np.array(crit2)

    # Replace values conditionally
    modified = np.where(lookup < 0.1, replace, lookup)

    # Apply criteria
    mask = (crit1 == crit1_val) & (crit2 == crit2_val)
    return np.max(modified[mask])

     

Resources