+ Reply to Thread
Results 1 to 12 of 12

Right function inside a Match function - highlight based on criteria

  1. #1
    Registered User
    Join Date
    05-30-2015
    Location
    New York
    MS-Off Ver
    2007
    Posts
    11

    Right function inside a Match function - highlight based on criteria

    Hi all,

    In a nutshell, I need to apply conditional formatting to a row (or multiple cells within a row if easier) using a match function, but only looking at the last 7 characters of each string (lookup value and lookup array)

    I have an ID column in data set 1, and an ID column in data set 2. If any of the ID's in ID column data set 2 appear in ID column data set 1, then I want to highlight in data set 1 the row that the match is found. I can put these data sets on the same sheet, different tabs, or even different workbooks, whatever makes it easier.

    Challenges: The ID's could have letters inside, and could have a leading zero, so they must be changed to text. For a given ID match, the leading zero may only be present in one of the instances.

    I'm close, but I'm not solving the leading zero problem. I'm having difficulties combining a right function with the lookup array (Inside the MATCH function).

    This is the conditional formatting formula that seems close to working:
    MATCH(RIGHT($D3,7),$E$6:$E$50,0)
    Obviously something needs to also indicate to take the RIGHT,7 for the lookup_array as well. I'm not sure if RIGHT is even working properly when within the MATCH function though.

    Please help. Thank you.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Right function inside a Match function - highlight based on criteria

    If you working with numbers try to convert value into number before matching:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-30-2015
    Location
    New York
    MS-Off Ver
    2007
    Posts
    11

    Re: Right function inside a Match function - highlight based on criteria

    As mentioned, there could be letters or leading zeros, so for all intents and purposes, I'm dealing with text.

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Right function inside a Match function - highlight based on criteria

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Right function inside a Match function - highlight based on criteria

    Please take the time and read the forumrules, expecialy the ones about crossposting.

    http://www.mrexcel.com/forum/excel-q...el-2007-a.html
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Right function inside a Match function - highlight based on criteria

    Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please in the future include links to any and all cross-posts in any other forums (not just this site).

  7. #7
    Registered User
    Join Date
    05-30-2015
    Location
    New York
    MS-Off Ver
    2007
    Posts
    11

    Re: Right function inside a Match function - highlight based on criteria

    Hello, I will be back on tonight and supply all the information asked for. Thank you for helping me.

    Linkback URL
    http://www.mrexcel.com/forum/excel-q...el-2007-a.html

  8. #8
    Registered User
    Join Date
    05-30-2015
    Location
    New York
    MS-Off Ver
    2007
    Posts
    11

    Re: Right function inside a Match function - highlight based on criteria

    Excel problems 2.JPG

    Hi all, above is an image of the problem. I thank you in advance.

    reminder, crosspost link is http://www.mrexcel.com/forum/excel-q...ml#post4171624

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Right function inside a Match function - highlight based on criteria

    Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping.

  10. #10
    Registered User
    Join Date
    05-30-2015
    Location
    New York
    MS-Off Ver
    2007
    Posts
    11

    Re: Right function inside a Match function - highlight based on criteria

    My apologies on earlier etiquette issues. I'm a newbie, but have now consolidated everything to this forum.
    I've noted as such and closed out the other forums.

    I have attached an Excel spreadsheet which I believe explains the problem very well.

    In addition, there is a second part to the problem (tab 2 of spreadsheet with explanation), which I had originally thought was not related.
    Creating the conditional formatting to resolve both at once with likely a consolidated IF statement will be the true art here.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-30-2015
    Location
    New York
    MS-Off Ver
    2007
    Posts
    11

    Re: Right function inside a Match function - highlight based on criteria

    Just some quick background on the 2nd part of the problem. I had originally thought it was the following question:
    If value in D is highlighted, then value in E=D; but since this highlighting is due to conditional formatting, the solution will all be within a conditional formatting (I think). Please see a post below which I believe is helpful in tying parts 1 & 2 together once part 1 is resolved:

    Posted by Rudi, edited by me to pertain to part 2 of our current problem:

    (in order to highlight and perform the part 2 task): You can use the same conditional expression from your conditional formatting within the IF() statement. There is no need to test if the cell is highlighted.

    For example:
    Assume the conditional formatting is set up like this in the D column to test value in C5 with value in H5 (a second data set):
    C/F: C5=H5, shade Yellow

    Your IF() function:
    In column E, type an IF function: =IF(C1=H1,D1,"")

    As you can see, the same C/F condition is just slotted into the IF() functions logical test.

  12. #12
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Right function inside a Match function - highlight based on criteria

    In the green cells you see if the length of the cells contains 7 characters.

    You could filter on the NON blanc cells.

    See the attached file.

+ 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. INDIRECT function not working inside MATCH with dynamic ranges
    By fotografer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-26-2014, 02:09 AM
  2. Replies: 0
    Last Post: 01-19-2013, 01:35 PM
  3. Choosing Name Range inside Index / Match Function
    By gallow in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-05-2012, 07:08 AM
  4. Index/Match function inside an array
    By tittiot in forum Excel General
    Replies: 2
    Last Post: 01-20-2010, 09:48 PM
  5. match function inside if function
    By gnotnoel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2006, 02:50 PM

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