Limit match_mode of XLOOPUP

Copper Contributor

Hi,

 

I make this example to show what I want to do:

Renaud_Herman_0-1614949944079.png

 

I have a table with numbers and a name in column C. For a list of numbers, I want to have the match name where the search value is included between A and B.

 

I use this function in cell: =IFERROR(XLOOKUP(A6;A$1:A$3;C$1:C$3);XLOOKUP(A6;B$1:B$3;C$1:C$3;"pas trouvé";1))

 

You can see that the value 21 is not included in the search table and is not included between A and B. I want throw an error or show a message when it's the case. 

 

I put 1 as match_mode in my second call but it works bad because 21 is not included between 22 and 30.

 

How can I do it ?

 

2 Replies

@Renaud_Herman 

Use FILTER() instead.

=FILTER($C$1:$C$3,($A$1:$A$3<=A6)*($B$1:$B$3>=A6),"pas trouvé")

@Detlef Lewin Thanks, I will try with it.