+ Reply to Thread
Results 1 to 7 of 7

Crosschecking between two sheets

  1. #1
    Registered User
    Join Date
    06-21-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Crosschecking between two sheets

    I have a workbook which hold two spreadsheets one is a list of work and one is a contact list, I need to be able to show if the work was done by a member of staff or by an external consultant.

    I have created a column (column C) in the first sheet and I want to be able cross check with the second spreadsheet and show in column C the words STAFF or CONSULTANT.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,104

    Re: Crosschecking between two sheets

    you can use index match or vlookup combined with an IF statement

    can you attach an example of the workbook , as not sure on your layout or how the data is presented - make sure its dummy information as this is a public forum

  3. #3
    Registered User
    Join Date
    06-21-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Crosschecking between two sheets

    Unfortunately the IT policies at my company won't allow me to show an example, however, the column I am interested is Column N (which shows peoples names) on the first sheet and what I want it to do is to cross check those names with the section sheet which holds the contact details of all the people who work for my company.

    If the name does not appear in the contact list then it want to show the word "Consultant" in column O on the first sheet. Conversely if the are a member of staff I want to show "Staff" in column O.

    Hope this helps.

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,104

    Re: Crosschecking between two sheets

    this should do the trick
    =IF(ISERROR(VLOOKUP(N1,Sheet2!$A$1:$A$6,1,FALSE)),"Consultant","Staff")

    names in Column N
    put the formula above in column O and copy down
    the reference of all company names is in
    sheet2 column A



    see attached for the example
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-21-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Crosschecking between two sheets

    There is still something not working correctly.

    I have tried to attach a sample sheet but again my IT policies prevent it. Can I email it direct?

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,104

    Re: Crosschecking between two sheets

    i have sent a PM with my email

    dont want emails on public forums

    also all support needs to remain on the boards here - so we do need to keep the questions and replies here
    i will try and upload , if the data appears to be OK to put on a public forum

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,104

    Re: Crosschecking between two sheets

    =IF(ISERROR(VLOOKUP(C2,'Staff List'!$C$2:$C$9,1,FALSE)),"Consultant","Staff")

    you are referencing the wrong cell
    you have C1 and need to look in C2 in the first formula in row C2

    also you changed the order - of consultant and staff

+ 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