+ Reply to Thread
Results 1 to 9 of 9

How to use conditional formatting and the VLOOKUP formula to find data in another sheet

  1. #1
    Registered User
    Join Date
    07-19-2019
    Location
    London, England
    MS-Off Ver
    MS Office 2016
    Posts
    9

    Red face How to use conditional formatting and the VLOOKUP formula to find data in another sheet

    Hi,
    I would appreciate any thoughts and help on why my conditional formatting only seems to work sporadically. I've tried looking in the forums but as I am not exactly sure what the problem is I spent lots of time reading - which was interesting - but not getting anywhere fast!

    I attach a workbook with a sample of the data. Two sheets, the main one, 'Personnel', with data that is pulled through to a 'Training Log' sheet (haven't finished that all yet).
    On the Training Log sheet I want the name of the person (column A) to be conditionally formatted (simple shading) based on data in column M (enrolment code) of the Personnel sheet. So, the formula has to find the same name and then look along the row to check if there is an entry (sorry if this is like asking you to suck eggs!). If it is possible to indicate through conditional formatting based on the 5 enrolment codes which one (5 different shaded colours) that would be great...starting simply however, I have used the formula below just to see if there is an entry:

    =NOT(ISBLANK(VLOOKUP(A3,Name_Data,9,FALSE)))

    but it doesn't always work!

    I am using the named range 'Name_Data' as I understand the formula won't work using the reference "Personnel!E2:Q12".

    Please be aware the Personnel sheet normally contains several hundred entries. Maybe because of this, or because I cannot put them in ascending order, the formula above does not seem to work reliably.

    Weirdly, it works fine on this smaller sample set of data.

    Any help would be much appreciated. For example, I was going to try using MATCH and INDEX but am not quite sure how to start since this is not a normal cell formula, but a conditional formatting one.

    Thanks very much for your thoughts!


    Nomades
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: How to use conditional formatting and the VLOOKUP formula to find data in another shee

    =VLOOKUP(A3,Name_Data,9,)<>0

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How to use conditional formatting and the VLOOKUP formula to find data in another shee

    Which case doesn't work? It is working correctly in all seven cases in your example.

    To troubleshoot a problem, we have to actually see the problem.

    I am using the named range 'Name_Data' as I understand the formula won't work using the reference "Personnel!E2:Q12".
    This is incorrect. That reference will work just fine.

    Please be aware the Personnel sheet normally contains several hundred entries. Maybe because of this, or because I cannot put them in ascending order, the formula above does not seem to work reliably.
    Size doesn't matter. By using FALSE as the last argument, you do not need to have the data sorted.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: How to use conditional formatting and the VLOOKUP formula to find data in another shee

    or
    =VLOOKUP(A3,Personnel!$E:$M,9,)&""<>""

  5. #5
    Registered User
    Join Date
    07-19-2019
    Location
    London, England
    MS-Off Ver
    MS Office 2016
    Posts
    9

    Re: How to use conditional formatting and the VLOOKUP formula to find data in another shee

    Thank you Tim201110. As my reply to the other answer, I think there must be a problem with my data as this doesn't work with the full pack. No doubt your solution will when I sort out my data. Much appreciated as it helped me narrow down where the problem is.

  6. #6
    Registered User
    Join Date
    07-19-2019
    Location
    London, England
    MS-Off Ver
    MS Office 2016
    Posts
    9

    Re: How to use conditional formatting and the VLOOKUP formula to find data in another shee

    Thanks 6StringJazzer. I think there must be some issue with my dataset because the sample worked fine and your comments mean I have not written the formula wrong necessarily. My first post on the forum so appreciate your pointing out a couple of things for the future.
    Ta

  7. #7
    Registered User
    Join Date
    07-19-2019
    Location
    London, England
    MS-Off Ver
    MS Office 2016
    Posts
    9

    Re: How to use conditional formatting and the VLOOKUP formula to find data in another shee

    Thanks CAABYYC. I will try this. Think the real problem is with my dataset not the formula so much. Much appreciated.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,896

    Re: How to use conditional formatting and the VLOOKUP formula to find data in another shee

    Check your data for stray spaces, or for line breaks. Was your raw data downloaded from the www?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  9. #9
    Registered User
    Join Date
    07-19-2019
    Location
    London, England
    MS-Off Ver
    MS Office 2016
    Posts
    9

    Re: How to use conditional formatting and the VLOOKUP formula to find data in another shee

    Thanks for the suggestions Glenn.
    - No, I entered the data myself, although part was copied and pasted
    - Suspect that's where the problem is. It seemed odd that formula I entered worked intermittently, not how these things are normally, either they work or they don't!

+ 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. Trying to find a formula/conditional formatting for this issue
    By darkcradle in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-20-2018, 05:48 AM
  2. Replies: 13
    Last Post: 04-16-2017, 02:32 AM
  3. [SOLVED] Conditional Formatting where cell value is result of VLOOKUP formula
    By smashthegas in forum Excel General
    Replies: 2
    Last Post: 02-16-2016, 04:31 PM
  4. [SOLVED] Help with a VLookup Formula in Conditional Formatting, multiple triggers
    By szm187 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-02-2016, 06:43 PM
  5. Not able to find invalid data properly with conditional formatting
    By Neelima Chowdhary in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-23-2014, 06:50 PM
  6. Conditional Formatting using Vlookup or Find
    By andyfry in forum Excel General
    Replies: 2
    Last Post: 01-29-2012, 09:25 PM
  7. [SOLVED] Conditional formatting on cells with a VLOOKUP formula in them
    By JenniM in forum Excel General
    Replies: 4
    Last Post: 04-01-2005, 02:06 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