+ Reply to Thread
Results 1 to 5 of 5

array match formula fails but simple match works

  1. #1
    Registered User
    Join Date
    05-27-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    array match formula fails but simple match works

    In sheet 1, I have
    Col A Col B
    John Sedgwy - R

    In sheet 2, I have the following names
    Col A Col B
    Peter Walker
    John Sedgwicky

    When I did an array match -> ={MATCH(1,(TRIM($a2)=Sheet2!$A$1:$A$2)*(left(b2,5)&"*"=Sheet2!$b$1:$b$2),0)}
    gave me an error but when I did an individual match to both John and Sedgw, it works. tried googling and multiple ways. Nothing works. Any idea why?

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: array match formula fails but simple match works

    maybe
    =MATCH(TRIM($A2)&LEFT(B2,5)&"*",Sheet2!$A$1:$A$2&Sheet2!$B$1:$B$2,0) or better the sumproduct

    =SUMPRODUCT((TRIM($A2)=Sheet2!$A$1:$A$2)*(ISNUMBER(SEARCH(LEFT(B2,5),Sheet2!$B$1:$B$2))))
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    05-27-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Unhappy Re: array match formula fails but simple match works

    Both formulas gives an error vlady.

    I cannot do sumproduct anyway as I need the row number returned from match, to identify related fields to person from sheet2. I also noticed similar queries in the forum but the replies are not what I am after.
    Can anyone help?

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: array match formula fails but simple match works

    what is the error received?

  5. #5
    Registered User
    Join Date
    05-27-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: array match formula fails but simple match works

    I typed out a long reply with all detailed calcuations and lost it because my login timed out

    the sumproduct formula actually worked ! My bad there... now how will I use it to get a corresponding field like the person's DOB ?
    I also found another formula online using ROW reference but got zeros.

    Your match formula only worked with exact matches, not if I need to use shorter words using LEFT function. attached file with all my attempts and various errors .....

    I suspect the main issue is the wildcard and/or non-exact matches !!
    Attached Files Attached Files

+ 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. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  2. [SOLVED] simple MATCH formula fails on array
    By djarum11 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-02-2014, 06:27 AM
  3. Range.Find function fails to find a match but For loop confirms that match exists
    By 6StringJazzer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-08-2013, 03:35 PM
  4. Index, match, match Question!. Seems so simple but can't figure it out...
    By huikimhuikim in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-12-2013, 01:57 PM
  5. Nested Match(index()) vs. Match() array formula.
    By GeneralDisarray in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2011, 07:57 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