Forum Discussion

Sintra950's avatar
Sintra950
Copper Contributor
Apr 28, 2023
Solved

Search a value in a table partially matching

Hi forum,

I need help because I am out of ideas. I need to get current emails for my users based on previous emails they could have.

I tried combining functions search, vlookup, match, index, etc. but I think I am not even close... Search is not working looking for values in a table or they work with exact match

I leave an example. How would you do it?

Info I look for:

 

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 29, 2023

    Sintra950 

    Yes, we search in a column value by value. SEARCH itself returns the spill, we take from it first matched position. Since you are on 365 it could be modified a bit

    =XLOOKUP(
    TRUE,
    ISNUMBER(SEARCH(E4, previous)),
    current,
    XLOOKUP(E4, current, current, "no such")
)

    Or you may generate the spill with all results at once

    =SCAN(
    "",
    value,
    LAMBDA(_,v,
        XLOOKUP(
            TRUE,
            ISNUMBER(SEARCH(v, previous)),
            current,
            XLOOKUP(v, current, current, "no such")
        )
    )
)

    Please check in attached file

5 Replies

    • Sintra950's avatar
      Sintra950
      Copper Contributor
      Apr 29, 2023

      SergeiBaklan 

      The problem I think the Search function, as I know is searching in a string (cell). I need to search in a column. Here is when I tried use it 

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 29, 2023

        Sintra950 

        Yes, we search in a column value by value. SEARCH itself returns the spill, we take from it first matched position. Since you are on 365 it could be modified a bit

        =XLOOKUP(
    TRUE,
    ISNUMBER(SEARCH(E4, previous)),
    current,
    XLOOKUP(E4, current, current, "no such")
)

        Or you may generate the spill with all results at once

        =SCAN(
    "",
    value,
    LAMBDA(_,v,
        XLOOKUP(
            TRUE,
            ISNUMBER(SEARCH(v, previous)),
            current,
            XLOOKUP(v, current, current, "no such")
        )
    )
)

        Please check in attached file

Resources