0

I'm playing with XLOOKUP with wildcards (Match_Mode 2). I believe it used to be if you want it to return results where a item in that excel it was = =XLOOKUP(""&"Text"&"",A:A,B:B,0,2), if it was only before =XLOOKUP("Text"&"*",A:A,B:B,0,2).

I'm finding now you can't get just before nor just after and you don't need "*"& just the star. =XLOOKUP("text*",A:A,B:B,0,2) The last comment is shown in red in the image.

Please take a look at the image below and provide thoughts

enter image description here

Thanks, Brad

Thought this might help.

Improve this question
17
  • If you are hardcoding the word within quotes, then "Beyond*" or "*Beyond*" or "*Beyond" as per your needs but if you are not then you will need to use in this way --> "*"&D1&"*" or "*"&D1 or D1&"*" as per your requirements. But what is your question, what are you trying to accomplish ? is it only the use of * wildcard operator within quotes while using the word hardcoded? Also using XLOOKUP() will return only the first occurrence while for multiple records you will need FILTER() function, I know you are aware of this still --> =FILTER(B4:B6,ISNUMBER(SEARCH(D1,A4:A6)))
    – Mayukh Bhattacharya
    Commented Apr 29 at 19:13
  • Thanks for reaching back out so quickly. I realize it would only return the first result. The formulas in the screen caps are =formulatext. What suggesting it I was expecting that I needed ""&D1&"" or ""&D1 as you suggest but it allow works "Beyond" or "*Beyond" which I wasn't expecting. Wondered if something changed.
    – BradR
    Commented Apr 29 at 20:03
  • This is not going to work: =XLOOKUP(""&D1&"",A4:A6,B4:B6,,2) it will return an #N/A error. You will need the wildcard operator. =XLOOKUP(D1&"*",A4:A6,B4:B6,,2). May be i am not understanding your requirements clearly or i am missing something.
    – Mayukh Bhattacharya
    Commented Apr 29 at 20:08
  • What I was trying to say, you cant use empty strings concatenated with lookup value and use matchmode for wildcard character. it won't work you need to supply one of the wildcard character operators as per your needs. it can be * or ? or ~
    – Mayukh Bhattacharya
    Commented Apr 29 at 20:16
  • @BradR please surround your "code" in back ticks ` so the * does not italicize the text.
    – Scott Craner
    Commented Apr 29 at 20:27

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .