Xlookup not searching entire Lookup_Array for Lookup Value.

[pawcoyote]

Good morning,

I am sure this is pretty simple but for some reason I cannot figure this out.

I have a number in column B2 that I want to use as the Lookup Value. On another spreadsheet my Lookup_Array is in Column E, but it has more than one number in it separated by either a " ; " or " , ".

I want to search that Lookup_Array for the number in B2 and return a value in Column F.

Below is the code but it is not searching the entire cell for the Lookup Value.

=IF($B$1="All Sites","",XLOOKUP($B$2,MidTerm!E:E,MidTerm!F:F,""))

 

[pawcoyote]

Good morning,

I am sure this is pretty simple but for some reason I cannot figure this out.

I have a number in column B2 that I want to use as the Lookup Value. On another spreadsheet my Lookup_Array is in Column E, but it has more than one number in it separated by either a " ; " or " , ".

I want to search that Lookup_Array for the number in B2 and return a value in Column F.

Below is the code but it is not searching the entire cell for the Lookup Value.

=IF($B$1="All Sites","",XLOOKUP($B$2,MidTerm!E:E,MidTerm!F:F,""))

I have updated the code to be as such and no go.

=IF($B$1="All Sites",""XLOOKUP("*"%$B$2,MidTerm!E:E,MidTerm!F:F,""))