The Excel formula XMATCH has a third argument that returns a value if the value being tested is EITHER greater OR less than the list of values. I need it to return a numeric value in all cases - Less than the minimum (7 in the example below), within the values (1-7 in the example) or greater than the maximum (1 in the example) .

Example

I'm trying to avoid an IF that tests for #N/A. The real use of the XMATCH in this case is inside an INDEX function and it gets hard to read / debug with extra IF logic.

Any suggestions?

CodePudding user response：

Cannot see a way to do it without any type of error checking - but this is fairly parsimonious:

=MAX(IFERROR(XMATCH(J1,C1:I1,{-1,1}),""))

Caveat: if there are two valid solutions (e.g. as for lookup -50% in second row), you can alternate between which value you want by changing max to min, or return both by simply removing the outer function altogether, depends upon your circumstances...

Aside

Note, whilst this part is outside the scope of your core Q, I thought it worth mentioning that you may want to explore other Office 360 functions to assist with lengthy / compound functions - as you've alluded. An excellent e.g. is 'let' which would allow you to define this function upfront, and then refer back to it as many times as you need to within a compound function. The obvious advantage is that you only need to check it once; further, if used apprporiately, it can compresses/shorten the length of the compound function considerably.

Here's an example the uses the above function to lookup the result in one index, and then a second index if not found in the first..

=LET(x_,MAX(IFERROR(XMATCH(J1,C1:I1,{-1,1}),"")),IFERROR(INDEX(N$2#,MATCH(x_,M$2#,0)),INDEX(Q$2#,MATCH(x_,P$2#,0))))

CodePudding user response：

What about XLOOKUP() which has the ability to handle errors:

=XLOOKUP(J1,C1:I1,SEQUENCE(,7),7,-1)