+ Reply to Thread
Results 1 to 17 of 17

Wildcard?non Exact match Array formula

  1. #1
    Registered User
    Join Date
    01-20-2015
    Location
    london
    MS-Off Ver
    2010
    Posts
    35

    Wildcard?non Exact match Array formula

    Hi I have the following array formula:

    ={IFERROR(SMALL(IF(MATERIAL!$C$2:$C$556480=$A$1,IF(MATERIAL!$N$2:$N$556480=$A$2,
    ROW(MATERIAL!$C$2:$C$556480)-ROW(MATERIAL!$B$2)+1)),ROWS($A$4:A4)),"")}

    This currently works perfect only now I have to search for non exact matches eg.
    Current i am searching for all instance of A2=M001
    A2 how ever can be M101 or and combination of digits it will however always begin with the character M.
    Does anyone know how to modify the array formaula to return all instance if begins with "M".
    Many thanks in advance!!
    I think will involve search & including wildcard - unsure of this

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Wildcard?non Exact match Array formula

    It would be lot easier to work on this type question if there were a sample file attached. That said, if you are sure that you will always be searching for the first, or left-most, character in A2 then
    Please Login or Register  to view this content.
    should work. Let me know if you have any questions.

  3. #3
    Registered User
    Join Date
    01-20-2015
    Location
    london
    MS-Off Ver
    2010
    Posts
    35

    Re: Wildcard?non Exact match Array formula

    Hi,

    I have tried this and does not seem to be working. Have you any other suggestions? Thanks

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Wildcard?non Exact match Array formula

    I'd suggest that you use the "Go Advanced" button and upload a small desensitized copy of your spreadsheet with the results as you would like to have them manually displayed.

    Let me know if you have any questions.

  5. #5
    Registered User
    Join Date
    01-20-2015
    Location
    london
    MS-Off Ver
    2010
    Posts
    35

    Re: Wildcard?non Exact match Array formula

    Please see attached file. It currently workswhen I specify exact mactch value in A2 hower as previously discussed need to be partial match.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Wildcard?non Exact match Array formula

    Looking at the file, I don't see the array formula that you talked about in your original post. I assume that it should be used in either column B,C or D of the Formula sheet, but without knowing which column and without knowing what results you expect to see in those columns it is hard to know how to help.

  7. #7
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Wildcard?non Exact match Array formula

    Hi All,

    not sure to have understood

    Please Login or Register  to view this content.
    array entered

    or


    Please Login or Register  to view this content.
    just enter.

    Hope it helps
    Attached Files Attached Files
    Last edited by canapone; 10-06-2015 at 10:25 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  8. #8
    Registered User
    Join Date
    01-20-2015
    Location
    london
    MS-Off Ver
    2010
    Posts
    35

    Re: Wildcard?non Exact match Array formula

    hi on the example file attached as it stands with search value of A2 being M001 it is returning only 10 instances I need it to return all 14 instances and catch the other 4nr entrys that match A1 and being with "m".

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Wildcard?non Exact match Array formula

    I see now. Testing @canapone's answers, they work for all 14 instances.

  10. #10
    Registered User
    Join Date
    01-20-2015
    Location
    london
    MS-Off Ver
    2010
    Posts
    35

    Re: Wildcard?non Exact match Array formula

    hi i have tried this while it does return all 14 instances, if you delete say sheet("material") cell a1 or change it to say x001, your formula still lists all instances when it should only list 13nr any ideas?

  11. #11
    Registered User
    Join Date
    01-20-2015
    Location
    london
    MS-Off Ver
    2010
    Posts
    35

    Re: Wildcard?non Exact match Array formula

    i can't seem to figure it out

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Wildcard?non Exact match Array formula

    Here is a solution that may help. It uses the value in A1 as well as the value in A2 and also uses the columns in the Material worksheet regardless of their order. If you don't want a column, don't include it in the formula worksheet. If you add columns or change their names, make the appropriate change to the column headers in the formula worksheet.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Wildcard?non Exact match Array formula

    Duplicated message please delete
    Last edited by newdoverman; 10-06-2015 at 12:05 PM.

  14. #14
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Wildcard?non Exact match Array formula

    Duplicate message please delete (forum troubles)
    Last edited by newdoverman; 10-06-2015 at 11:48 AM.

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Wildcard?non Exact match Array formula

    @ crossfitciaran, I put in @ canapone's Iferror(Aggregate...) formula and I am not seeing what you are talking about. If I change MATERIAL!N2 to x001 then FORMULA!A4 changes to 2, so it is only listing the 13 near matches (2 - 14).

  16. #16
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Wildcard?non Exact match Array formula

    Please delete...file already uploaded
    Attached Files Attached Files
    Last edited by newdoverman; 10-06-2015 at 12:08 PM.

  17. #17
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Wildcard?non Exact match Array formula

    To get order numbers only, why it should not be COUNTIFS?

    In A4:

    =IF(ROW(1:1)<=COUNTIFS(MATERIAL!$C$2:$C$553927,$A$1,MATERIAL!$N$2:$N$553927,"M*"),ROW(1:1),"")
    Quang PT

+ 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. [SOLVED] array filter that returns text if an exact match only
    By mcdermott2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-10-2015, 06:13 PM
  2. Replies: 4
    Last Post: 01-06-2015, 09:15 PM
  3. Table Array with Wildcard involving partial URL match
    By tworoads in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-29-2014, 09:29 AM
  4. Replies: 4
    Last Post: 12-14-2009, 03:21 PM
  5. [SOLVED] Compare Two Columns (Exact and Wildcard)
    By Xavier in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-15-2006, 01:35 PM
  6. [SOLVED] how to use * wildcard in a sum(if((cond),range)) array formula
    By Bruce in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-09-2006, 09:35 PM
  7. Wildcard Character in an Array Formula
    By Scorpvin in forum Excel General
    Replies: 2
    Last Post: 09-30-2005, 02:05 PM

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