+ Reply to Thread
Results 1 to 9 of 9

Can INDEX MATCH do what I need here?

  1. #1
    Registered User
    Join Date
    09-04-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    7

    Question Can INDEX MATCH do what I need here?

    Hi,

    I have two data sources:
    1. A list of email addresses for people at my company that have been invited to use a beta tool
    2. The complete user list and their activity exported from the beta tool


    Right now, when I want to see who from the invite list has been using the tool, I use VLOOKUP to extract the data from the beta user activity list. Someone told me that the Index Match function would be a better, cleaner way to do this but I'm not able to get it to work.

    What would the formula be if I was trying to compare this. Right now my two table headers look like this:

    SHEET 1: INVITE LIST
    Employee Name | Email Address | Date Joined | Total Logins | Total Time Spent

    (I want to populate the Date Joined, Total Logins & Total Time Spent from this sheet #2

    SHEET 2: USER ACTIVITY LOG
    Email Address | Date Joined | Total Logins | Total Time Spent | Date Last Login |# of Referrals

    I would GREATLY appreciate any help in understanding what the INDEX MATCH formula would be to fill in the Data Joined column for example using sheet 2 as my array. The one field that both sheets have in common is the Email Address.

    Tim

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

    Re: Can INDEX MATCH do what I need here?

    You can put this formula in C2 of Sheet1:

    =IFERROR(INDEX(Sheet2!$B:$D,MATCH($B2,Sheet2!$A:$A,0),COLUMNS($B:B)),"")

    or alternatively:

    =IFERROR(INDEX(Sheet2!B:B,MATCH($B2,Sheet2!$A:$A,0)),"")

    Then you can copy that formula into D2 and E2. Format those cells in the style you prefer, then copy down as required.

    Incidentally, the VLOOKUP version would be:

    =IFERROR(VLOOKUP($B2,Sheet2!$A:$D,COLUMNS($A:B)),"")

    which is a bit shorter.

    Hope this helps.

    Pete
    Last edited by Pete_UK; 09-04-2018 at 01:42 PM.

  3. #3
    Registered User
    Join Date
    09-04-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Can INDEX MATCH do what I need here?

    THANK YOU. You are a life saver!

  4. #4
    Registered User
    Join Date
    09-04-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Can INDEX MATCH do what I need here?

    What would the formula look like if my headers were like this:

    SHEET 1: INVITE LIST
    Employee Name | Email Address | Date Joined | Total Logins | Total Time Spent

    (I want to populate the Date Joined, Total Logins & Total Time Spent from this sheet #2

    SHEET 2: USER ACTIVITY LOG
    Employee Name | Email Address | Date Joined | Total Logins | Total Time Spent | Date Last Login |# of Referrals

    I would still need to use Column B as the constant match because an employee often uses a nickname in registering, so it's not a 100% match to my records.

    Thanks again in advance. I greatly appreciate your help.

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

    Re: Can INDEX MATCH do what I need here?

    Which formula do you want me to change (I gave you three)?

    Also, do you want to pick up the name from the other sheet as well?

    Pete

  6. #6
    Registered User
    Join Date
    09-04-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Can INDEX MATCH do what I need here?

    If you can show me how to do it with these two that'd be great. I am familiar with Vlookup.

    =IFERROR(INDEX(Sheet2!$B:$D,MATCH($B2,Sheet2!$A:$A,0),COLUMNS($B:B)),"")

    or alternatively:

    =IFERROR(INDEX(Sheet2!B:B,MATCH($B2,Sheet2!$A:$A,0)),"")

    You can keep it as sheet2, that's fine. Thanks again.

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

    Re: Can INDEX MATCH do what I need here?

    The only difference here is that you have the name in column A of Sheet2, so that the columns where you are getting data from (and the one that you are searching) have moved over. The second formulae thus becomes:

    =IFERROR(INDEX(Sheet2!C:C,MATCH($B2,Sheet2!$B:$B,0)),"")

    If you want to return the Employee Name to A2 of Sheet1, which you can't do very easily using VLOOKUP), then you can use this:

    =IFERROR(INDEX(Sheet2!A:A,MATCH($B2,Sheet2!$B:$B,0)),"")

    which is just the above formula copied from C2 into A2.

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    09-04-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Can INDEX MATCH do what I need here?

    Thank you so much. This has been very helpful!

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

    Re: Can INDEX MATCH do what I need here?

    Glad to hear it, and thanks for the rep yesterday.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Pete

+ 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. [SOLVED] INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?
    By Renejorgensen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2016, 10:54 AM
  2. [SOLVED] Index / Match - match 3 input values and return the results from the index
    By t83357 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2016, 07:34 PM
  3. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  4. INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges
    By Andrew-Mark in forum Excel General
    Replies: 3
    Last Post: 02-27-2015, 10:56 PM
  5. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  6. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  7. Replies: 3
    Last Post: 05-02-2013, 01:31 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