+ Reply to Thread
Results 1 to 3 of 3

Lookup or match over several columns

  1. #1
    Registered User
    Join Date
    08-13-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2016,2016,365
    Posts
    92

    Lookup or match over several columns

    Hello

    Hoping you can help.

    I have a master list of employees with Uniq ID based on their Staff ID, Name, DOB and NI number.

    Each month I do a headcount report and a lookup from the masterlist to headcount sheet to apply their UnqID number.

    If there staff number has not changed then thats fine but there is always a batch that return #N/A so I have to work out if they are new starts or if they are employees that have left and joined again with new Staff ID.

    So I think I need to work out if their name appears in the master list and then if it does, then work out if their DOB or NI matches(don't always have that) and then I will know that they already have a UNQ ID. I have attached a sample if anyone can help me that would be appreciated.

    Thanks

    Ross
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,145

    Re: Lookup or match over several columns

    You could use a formula like this in F2 of the Headcount sheet:

    =COUNTIFS('Master list '!B:B,B2,'Master list '!C:C,C2,'Master list '!D:D,D2)

    Copy down, and if it returns zero then you need to check the Master sheet and maybe add details.

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2019; 365
    Posts
    224

    Re: Lookup or match over several columns

    And to navigate more easily in the Master sheet, assuming there will be many names in there, you can add in G2 and drag down the following formula which will make a hyperlink to the cell in the Master list that corresponds to the name in Headcount sheet in column B.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I made it up to 499 names (B2:B500) but you can increase it if you have more names in the Master list.
    In G2 it displays the value $B$2 with hyperlink to B2 in Master list, and so on.
    The -33 in the end of the formula removes the name of the workbook which would also appear - instead of just $B$2, it would appear '[lookup demo.xlsx]Master list '!$B$2

    If it's #N/A like in the case of John, it means that the name doesn't exist in the Master list at all.

    I hope this helps.

+ 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: 7
    Last Post: 06-19-2011, 12:51 PM
  2. Lookup across 2 or more columns to match a row
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 07:05 AM
  3. [SOLVED] Lookup across 2 or more columns to match a row
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 06:05 AM
  4. Lookup across 2 or more columns to match a row
    By Cara in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  5. [SOLVED] Lookup across 2 or more columns to match a row
    By Cara in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  6. Lookup across 2 or more columns to match a row
    By Cara in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] Lookup across 2 or more columns to match a row
    By Cara in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  8. [SOLVED] Lookup across 2 or more columns to match a row
    By Cara in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

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