Results 1 to 3 of 3

Thread: how to add a partial text search to complicated index and match formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default how to add a partial text search to complicated index and match formula

    Hi I have a complicated index and match formula but I cannot figure out how to add a partial text search to it. Eg how do I add search for "apple*" without an array.

    thank you.....here is the current formula. alex

    =INDEX(Sheet2!E5:E1346,MATCH(Chooser!E26&Chooser!E23&Chooser!E29,Sheet2!B5:B1346&Sheet2!C5:C1346&Sheet2!D5:D1346,0),1,1)

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,645
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: how to add a partial text search to complicated index and match formula

    See if this works for you:

    =INDEX(Sheet2!E5:E1346,MATCH(1,INDEX(ISNUMBER(SEARCH(Chooser!E26,Sheet2!B5:B1346))*(Sheet2!C5:C1346=Chooser!E23)*(Sheet2 !D5:D1346=Chooser!E29),0),0))

    Ive assumed apple is in E26 and to be found in B5:B1346.

  3. #3
    New Member
    Join Date
    Jan 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to add a partial text search to complicated index and match formula

    Quote Originally Posted by steve the fish View Post
    See if this works for you:

    =INDEX(Sheet2!E5:E1346,MATCH(1,INDEX(ISNUMBER(SEARCH(Chooser!E26,Sheet2!B5:B1346))*(Sheet2!C5:C1346=Chooser!E23)*(Sheet2 !D5:D1346=Chooser!E29),0),0))

    Ive assumed apple is in E26 and to be found in B5:B1346.

    yes thanks, or close enough that I followed your lead and found the solution!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •