Forum Discussion
max corresponding to a value which may be in 1 or multiple sets
Hi
G6 is showing error because F6 is not found in D15:D50. what would be the simple process that F6 is searched in D15:D50 and D56:D91 and returns value from col K instead of an error whether or not that value exist in either of the 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.
2 Replies
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.
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.
