Forum Discussion
max corresponding to a value which may be in 1 or multiple sets
That formula should work, provided that your objective to find an exact match of -917 in both ranges for column D. There is no match in both you'll get #N/A.
Use the optional argument [march_mode] to indicate your intentions is an exact match is not found as illustrated in the screenshot below.
If you require to choose the maximum of a number of exact matches you must ensure that the failed matches do not return #N/A because that will cause MAX to give an error. The 4th parameter of XLOOKUP will let you return an alternative value for failed matches; in this case 0 would be appropriate provided the numbers you seek are positive.
As Riny suggests, when searching for numerical values it is more common to use an inexact search for the next largest value.