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. ...
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

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

  • Sintra950's avatar
    Sintra950
    Copper Contributor
    Jun 25, 2023
    Thanks so much, it worked. It helped me a lot.

Resources