+ Reply to Thread
Results 1 to 9 of 9

Vlookup Duplicate values

  1. #1
    Registered User
    Join Date
    06-21-2019
    Location
    US
    MS-Off Ver
    Excel
    Posts
    4

    Exclamation Vlookup Duplicate values

    Hello everyone,

    I have a question in regards to vlookup. I'm extracting data from a directory of employees using their last names with vlookup, and obviously there are many employees with the same last name which can cause error in the data.

    How can I resolve this? This has become really trouble some for me and even if there's a way to have vlookup return no data for duplicates while giving data for the unique last names it would be a great help.

    Many thanks.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Vlookup Duplicate values

    Hello and welcome to the forum.

    I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  3. #3
    Registered User
    Join Date
    06-21-2019
    Location
    US
    MS-Off Ver
    Excel
    Posts
    4

    Re: Vlookup Duplicate values

    Attachment 629200

    This is the worksheet with the vlookup, I simply want the location of each person based on their last name however, many of the suite numbers vlookup is showing is wrong because there can be multiple "brown"'s or "carpenter"'s in the directory and vlookup shows the suite number for the first of the duplicates.

    Attachment 629201

    This is the directory from where I am extracting the location. It's basically a list of all employees and their suite numbers. And as you can see, there are three "alexander"'s, and if I inputted alexander into vlookup, it would give me the suite number for the first one, when really I needed the second or third alexander. I don't want it to do that, I want it to return no value when there are duplicates so I can manually input the data there by looking it up myself instead of giving me something incorrect.

    Thank you for your help
    Last edited by thussain1; 06-21-2019 at 12:36 PM.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Vlookup Duplicate values

    Deleted, misunderstood your request, I thought you had both last names and first names but with only last names a different solution is needed.
    Last edited by Sam Capricci; 06-21-2019 at 01:36 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Vlookup Duplicate values

    I'd like to see if there is a better option than having you manually go through the duplicates, which is why I'd like to see a sample of what you have available to you.

    Your sample workbook attachments are invalid.

    Follow the steps outlined in post #2 to post a sample workbook.

    If you only want to do the VLOOKUP when the last names are unique, you can try something like this:

    =IF(COUNTIF(A:A,A2)>1,"",vlookup_formula_goes_here)
    Last edited by 63falcondude; 06-21-2019 at 01:12 PM.

  6. #6
    Registered User
    Join Date
    06-21-2019
    Location
    US
    MS-Off Ver
    Excel
    Posts
    4

    Re: Vlookup Duplicate values

    Here is the attachment
    Attached Files Attached Files

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Vlookup Duplicate values

    Try this in B3:

    =IF(COUNTIF(Directory!A:A,A3)>1,"",VLOOKUP('Directory Search'!A3,Directory!A:B,2,FALSE))

    Duplicates will show up as blank and no matches will show as #N/A.

  8. #8
    Registered User
    Join Date
    06-21-2019
    Location
    US
    MS-Off Ver
    Excel
    Posts
    4

    Re: Vlookup Duplicate values

    That works perfectly.

    Thanks!

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Vlookup Duplicate values

    Happy to help. Thanks for the rep!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. Vlookup with Duplicate values
    By Noman050 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-19-2018, 01:26 PM
  2. [SOLVED] VLOOKUP where Duplicate Values
    By rz6657 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-18-2016, 12:09 PM
  3. [SOLVED] VLOOKUP and Duplicate Values
    By marshak in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-03-2015, 01:43 AM
  4. Vlookup with duplicate values
    By bperkins in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-30-2013, 03:31 AM
  5. Need help with vlookup in duplicate values
    By vivshan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2013, 05:54 AM
  6. VLookup Duplicate Values
    By markd87 in forum Excel General
    Replies: 0
    Last Post: 07-20-2011, 11:54 AM
  7. Vlookup - Duplicate Values
    By sraju in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-28-2010, 09:09 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