+ Reply to Thread
Results 1 to 4 of 4

Wrapping Wildcard in Formula

  1. #1
    Registered User
    Join Date
    03-28-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    36

    Wrapping Wildcard in Formula

    Hi,

    I have the following formula:

    =IFERROR(INDEX(Sheet1!$A$25:$L$233,SMALL(IF(Sheet1!$E$25:$E$233=$A$1,ROW(Sheet1!$E$25:$E$233)-ROW(Sheet1!$E$24)),ROWS(A$25:A26)),MATCH(A$24,Sheet1!$A$24:$J$24,0)),"")

    where A1 is the text I'm trying to match to.

    This formula works fine, but I need to find a way to fold in a wildcard, so it picks up any instance of the word in A1. For example, if A1 = Bob, I also want it to return any matches that have Bobby listed.

    I've tried folding in "*&A1&*" into the formula as seen below, but it doesn't seem to work.
    =IFERROR(INDEX(Sheet1!$A$25:$L$233,SMALL(IF(Sheet1!$E$25:$E$233="*&A1&*",ROW(Sheet1!$E$25:$E$233)-ROW(Sheet1!$E$24)),ROWS(A$25:A26)),MATCH(A$24,Sheet1!$A$24:$J$24,0)),"")


    Any ideas would be greatly appreciated!!



    Thank you!

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Wrapping Wildcard in Formula

    "*&A1&*" is the text string *&A1&*, so the MATCH() function will search for text strings that contain &A1&. You need to use quotation marks to separate your text literals from your cell reference "*"&A1&"*"
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    12-26-2019
    Location
    seoul, south korea
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: Wrapping Wildcard in Formula

    Other method

    =IFERROR(INDEX(Sheet1!$A$25:$L$233,SMALL(IF(isnumber(find(a1,Sheet1!$E$25:$E$233)),ROW(Sheet1!$E$25:$E$233)-ROW(Sheet1!$E$24)),ROWS(A$25:A26)),MATCH(A$24,Sheet1!$A$24:$J$24,0)),"")

  4. #4
    Registered User
    Join Date
    03-28-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    36

    Re: Wrapping Wildcard in Formula

    This worked, Thank you both!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Wrapping an IF around an Array Formula?
    By Motox in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-17-2015, 12:09 AM
  2. Find Wildcard, return that Wildcard value?
    By help_me_im_lost in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-08-2014, 01:20 PM
  3. [SOLVED] Find: Using wildcard, but don't replace with wildcard
    By JimDandy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-06-2013, 05:39 PM
  4. Formula for wrapping the text
    By rms10 in forum Excel General
    Replies: 1
    Last Post: 06-26-2009, 05:34 PM
  5. Wrapping text on formula
    By maxthebear in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-25-2009, 05:37 PM
  6. Wrapping a formula in Sum()
    By teylyn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-02-2008, 10:34 PM
  7. Text Wrapping Cells that are part of a formula
    By Dahlman in forum Excel General
    Replies: 1
    Last Post: 01-26-2006, 02:00 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1