Forum Discussion
Ozzy7
Jul 22, 2025Copper Contributor
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...
Kidd_Ip
Jul 23, 2025MVP
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])