Using IF, Xlookup and Match/search, to find value in array.

Hi all,

I have a seemingly easy one, but I just can't get my formula correct.

I have a VBA Macro which needs to know what Australian States(and territory etc) are mentioned in an Array for each UserID

e.g: UserID: might mention NSW and VIC in a single row array.

I have a Cell for each State/territory for the Macro.

What I need to do is LOOKUP a UserID and if NSW appears anywhere in the array (A4:P4) then return "NSW".

I have attempted this a few ways and failed, happy to use VBA, but would like to use a formula if possible.

My current attempt looks something like:

Please Login or Register  to view this content.

This spills the array into multiple cells. I know I need a match or search etc in there, but I just can't seem to land it. I tried IF(ISTEXT(SEARCH etc, but I can't get my nesting with XLOOKUP and IF correct.

Thanks in advance

All good, solved it myself:

=IF(IFERROR(FIND("NSW",XLOOKUP(..........),1),"")<>"","NSW","")