+ Reply to Thread
Results 1 to 5 of 5

Removing Whole-Word Matching in Partial-Match Cross-Reference Tables

  1. #1
    Forum Contributor
    Join Date
    09-16-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 2013
    Posts
    107

    Removing Whole-Word Matching in Partial-Match Cross-Reference Tables

    I have a cross-reference table in the tab "ProdDescPartialXRef" with column A as partial matches (i.e. "WARMER") and column B as corrected descriptions (i.e. LEG WARMER). My other tab is "All Prods" and contains a list of descriptions that need to be corrected in Column A. Column B of that tab has the following formula to access the xref table:

    Please Login or Register  to view this content.
    Right now it is only whole-word matching (because of the spaces on either side of the tab reference), but if I remove the spaces it breaks the formula. Right now the xref value "WARMER" only matches something like "LEG WARMER TOY" but I would also like it to match "LEGWARMERTOY".

    I've attached my spreadsheet for your perusal. Any help would be appreciated. Thanks to NBVC for helping me get this far--and now, kind reader, would you help me make the final step?

    Dan
    Attached Files Attached Files
    Last edited by dipique; 04-26-2011 at 04:39 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Removing Whole-Word Matching in Partial-Match Cross-Reference Tables

    It would be better if you actually showed the same you reference in the post in the worksheet, so we know which way you are working....

    Try perhaps:

    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    09-16-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 2013
    Posts
    107

    Re: Removing Whole-Word Matching in Partial-Match Cross-Reference Tables

    Works like a charm... but I'm at a complete loss as to why. Would you mind dissecting that for me?

    And for that matter, telling me where on earth you got this good at Excel?
    Last edited by NBVC; 04-27-2011 at 09:30 AM. Reason: delete quote

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Removing Whole-Word Matching in Partial-Match Cross-Reference Tables

    The LOOKUP function looks for the last time a value in an array is smaller than or equal to a lookup value.

    Here we are looking for a 2 in the array produced by this condition: 1/((ProdDescPartialXRef!$A$2:$A$10<>"")*(ISNUMBER(SEARCH(ProdDescPartialXRef!$A$2:$A$10,'All Prods'!A2))))

    This condition will ultimately produce an array of 1's and #DIV/0! errors. The denominator produces 2 arrays of TRUEs and FALSEs muliplied together to give 1's and 0's (1 where 2 TRUEs coincide, and 0 where any other combination occurs). This condition: (ProdDescPartialXRef!$A$2:$A$10<>"") looks for non blanks in the lookup range and this condition: (ISNUMBER(SEARCH(ProdDescPartialXRef!$A$2:$A$10,'All Prods'!A2)) searches for each string in the lookup table and tries to find it in cell A2. If it does it returns a number marking the position within A2 that the string is found... otherwise #VALUE error is returned.... ISNUMBER simply checks if a number was returned... and results in TRUE if a number was found.

    1 divided by an array of 1s and 0s will produce an array of 1's and #DIV/0 errors.

    Now LOOKUP tries to find a 2 amongst those. The #DIV/O errors are ignored by the function and so the last entry that is less than or equal to 2 is the last 1 in the array.

    The corresponding item in the ProdDescPartialXRef!$B$2:$B$10 is then returned.

    If you go to the Formulas tab and click Evaluate Formula from the Formula Auditing section, you can follow along as the formula gets evaluated and you will see the steps described above.

  5. #5
    Forum Contributor
    Join Date
    09-16-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 2013
    Posts
    107

    Re: Removing Whole-Word Matching in Partial-Match Cross-Reference Tables

    I feel a little worshipful. Thank you very much for your help--I've learned a great deal from this experience.

    Dan
    Last edited by NBVC; 04-27-2011 at 09:31 AM. Reason: delete quote

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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