Limit match_mode of XLOOPUP

%3CLINGO-SUB%20id%3D%22lingo-sub-2187918%22%20slang%3D%22fr-FR%22%3ELimit%20match_mode%20of%20XLOOPUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2187918%22%20slang%3D%22fr-FR%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20make%20this%20example%20to%20show%20what%20I%20want%20to%20do%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Renaud_Herman_0-1614949944079.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F261184iCA2EE512988D9B38%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Renaud_Herman_0-1614949944079.png%22%20alt%3D%22Renaud_Herman_0-1614949944079.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%20numbers%20and%20a%20name%20in%20column%20C.%20For%20a%20list%20of%20numbers%2C%20I%20want%20to%20have%20the%20match%20name%20where%20the%20search%20value%20is%20included%20between%20A%20and%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20this%20function%20in%20cell%3A%20IFERROR%20(XLOOKUP%20(A6%3B%20A%241%3AA%243%3B%20C%241%3AC%243)%3B%20XLOOKUP%20(A6%3B%20B%241%3AB%243%3B%20C%241%3AC%243%3B%22%20not%20found%22%3B1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20see%20that%20the%20value%2021%20is%20not%20included%20in%20the%20search%20table%20and%20is%20not%20included%20between%20A%20and%20B.%20I%20want%20to%20throw%20an%20error%20or%20show%20a%20message%20when%20it's%20the%20case.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20put%201%20as%20match_mode%20in%20my%20second%20call%20but%20it%20works%20bad%20because%2021%20is%20not%20included%20between%2022%20and%2030.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20do%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2187918%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New 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.