+ Reply to Thread
Results 1 to 19 of 19

Help with Index/Match using names

  1. #1
    Registered User
    Join Date
    09-14-2012
    Location
    chantilly, va
    MS-Off Ver
    Excel 2007
    Posts
    9

    Exclamation Help with Index/Match using names

    I'm new to this kind of formula so if there is a better way to do either of the functions below, please let me know.

    1. I have a table of rates (Spreadsheet B). I want to pull dollar amounts from it into another spreadsheet (Spreadsheet A, Tab Backup). Spreadsheet B has full names (Mouse, Mickey M.), Spreadsheet A, Tab Backup only includes first and last names (Mouse, Mickey).

    2. Further, I need to reference the names in shortened format (Mouse, M.) in Spreadsheet A, Tab Table 1.

    I'm finding dashes and spaces and extra names (de Mouse, Mickey M.; Duke-Duck, Daisy L.; Lyn, Yo-Shi) are confusing me.

    For #1, I'm trying:
    =IFNA(INDEX('[Spreadsheet B.xlsx]Page1_1'!$H$5:$H$10,MATCH(A40&"*",'[Spreadsheet B.xlsx]Page1_1'!$A$5:$A$10,1)),"")
    It works most of the time, but some of the names throw it off. I'd like it to match exactly everything before the comma and just the first word after the comma.

    For #2, I'm trying:
    =IFERROR((LEFT(Backup!A17,FIND(", ",Backup!A17)+2)&".")," ")
    What I'm trying to do is to tell it to show everything before the comma and the first letter after the comma with a period (de Mouse, M.). It seems to be working, but let me know if I've missed anything or if there is an easier way.

    I've uploaded a short example of each file. Thanks for any help with this!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with Index/Match using names

    Why not put the employee list on workbookA on a separated sheet?

    The input can be organised with data-validation.

    The result can be made with pivot table.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    09-14-2012
    Location
    chantilly, va
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with Index/Match using names

    Thanks for your suggestion. However, the data has to stay in the files as is. Spreadsheet B changes frequently as employees come and go and rates change and it's not my spreadsheet so I can't make changes to it. Spreadsheet A needs to stay as is because you are just seeing a simplified version of it. There are many other calculations that go below the names on the Tab Backup and more tabs I didn't include that are linked to the information. I don't think a pivot table is what I need. I don't need to organize the data in Spreadsheet A, I basically just need to pull the rates from Spreadsheet B automatically.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with Index/Match using names

    In that case: The input can be organised with data-validation.

    You have to make sure there can be NO wrong input in your file, which causes trouble like you stated.

    data-validation is made to make sure the input is correct.

  5. #5
    Registered User
    Join Date
    09-14-2012
    Location
    chantilly, va
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with Index/Match using names

    Thanks for your help. I think I need more explanation or a visual of what you're suggesting. Note, I have 75+ files like Spreadsheet A that all need the rates pulled from Spreadsheet B automatically.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with Index/Match using names

    Who inputs the names in table 1 in spreadsheet A in the file?

    That is where you get the trouble?

    With a pivot table you won't have that problem, since it will that the names of sheet back up.

    If those names are writting correct, that will also solve your problem.

    See the attached file, where I add a pivot table.

  7. #7
    Registered User
    Join Date
    09-14-2012
    Location
    chantilly, va
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with Index/Match using names

    Thanks for your efforts, but that's not really what I'm looking for. It just adds another table that would have to be updated throughout all my many files. Wouldn't I would have to copy all the names (hundreds) from Spreadsheet B into the pivot table every time I use the file to make sure the pivot table list is up to date? Spreadsheet B is not a static file; it's being updated all the time. Plus, I don't need all that data inside each file. I'm usually using less than 20 names out of the hundreds available. The problem comes in when I receive which names I need to use, they aren't the entire name (ex: no middle initials). And while I could look up and enter the entire name, I don't want to use the entire name for the reports based off this information (Table 1) so I was trying to skip that manual step.

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with Index/Match using names

    The problem comes in when I receive which names I need to use, they aren't the entire name (ex: no middle initials).
    You did not answer my questions in #6.

    Who inputs the names in table 1 in spreadsheet A in the file?

  9. #9
    Registered User
    Join Date
    09-14-2012
    Location
    chantilly, va
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with Index/Match using names

    I manually input the names into spreadsheet A from a source completely separate from spreadsheet A and B. This source doesn't show full names (leaves out middle initials).

  10. #10
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with Index/Match using names

    Then the first step would be, to make sure that all names in all sheets are the same.

    This source doesn't show full names (leaves out middle initials).
    1) Maybe you can change this source?

    2) If not than don't work with the source and make a link to the sheet with the names (file B) in it.

    After that make sure you validate the input.

    This can be done with Data => datavalidation.

    3) Why are there so many differant files anyway (1 for each day) / 1 for each employee)?

  11. #11
    Registered User
    Join Date
    09-14-2012
    Location
    chantilly, va
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with Index/Match using names

    The other sources are not mine. The only thing I can change is how Spreadsheet A works. Do you have any suggestions on how to get the index/match to work?

  12. #12
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with Index/Match using names

    I don't think there will be an easy solution with that kind of data.

    1) Did you investigate the possibility of data-validation?

    If so, what is the result.

    2) Why don't you answer (all) the questions?

  13. #13
    Registered User
    Join Date
    09-14-2012
    Location
    chantilly, va
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with Index/Match using names

    1) Did you investigate the possibility of data-validation?
    If so, what is the result.
    I've never used data validation so I'm confused. How do the names get from Spreadsheet B into Spreadsheet A? Am I just copying the info from Spreadsheet B every time I open the file to make sure the pivot table has all the information I need in it (it changes frequently)? I didn't see any linking in the file you sent. If I have all the information inside the files (like Spreadsheet A), I would have to be able to hide and lock that tab because I can't have people seeing the rates of everyone else except those few that are in their project's file.

    2) Why don't you answer (all) the questions?
    I'm sorry, I thought I had. There is one file for each project and each project gets a new file every 2 weeks with lots of other updated information in it.
    Spreadsheet B (rate file) is coming from accounting.
    The names I input manually are coming from a timesheet system, hence the difference in their formats.

    Thanks for your persistence!

  14. #14
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with Index/Match using names

    in that case you could probably make at table of column 1) the timesheet and a table of column 2) the names from Sheet B.

    Then you can use vlookup to find the right names.

    But if you add the names manualy in sheet A, why not use the names from Sheet B in the first place?

    What is the problem showing names from file B in the file A (if there is no rates added to it)?

  15. #15
    Registered User
    Join Date
    09-14-2012
    Location
    chantilly, va
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with Index/Match using names

    The whole point is to not have to open Spreadsheet B at all. I get a list from somewhere of who worked on a project and I have to add them to Spreadsheet A if they aren't already there. This list has last name, first name. If I want their full name, I have to look up Spreadsheet B. If I put a copy of Spreadsheet B inside Spreadsheet A, I still have to open Spreadsheet B and update the list every time I update Spreadsheet A. So:
    1. Is there a way to automatically update or link the copy of Spreadsheet B that's inserted in Spreadsheet A?
    2. Is there a way to hide and lock just one tab in a workbook?

  16. #16
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Help with Index/Match using names

    I would suggest you make a helper column of sorts that standardizes the formats of names. You can do this with replace() once you determine your criteria and desired results.

    Failing that, I would suggest you look at text to columns and utilize that as a tool to standardize the names. The problem is that the names are input in a bunch of different formats, as far as I am aware, so you should just address the root cause instead of trying to create a monster formula that will account for all the different iterations of how someone can input the same name.

  17. #17
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Help with Index/Match using names

    1. Is there a way to automatically update or link the copy of Spreadsheet B that's inserted in Spreadsheet A?
    Yes, the most efficient way I can think of requires VBA and enabling macros.

    2. Is there a way to hide and lock just one tab in a workbook?[/QUOTE]
    Yes. Hide the tab then protect the structure of the workbook. This will prevent anyone from unhiding it, adding new tabs, etc without the password.

  18. #18
    Registered User
    Join Date
    09-14-2012
    Location
    chantilly, va
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with Index/Match using names

    I finally realized I'd have some other hurdles if Spreadsheet B data was in a different file. I did end up using a data validation table inside Spreadsheet A and the Vlookup function. It'll make me have to do some cover up if I end up needing to share the file, but made sense for most of the time. It's been quite a learning curve, thank you all for your suggestions and help. And persistence when I was resisting your good ideas.

  19. #19
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with Index/Match using names

    You're welcome. We appreciate the feedback!

    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

    It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

+ 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 on two worksheets with duplicate names
    By score in forum Excel General
    Replies: 4
    Last Post: 08-06-2016, 07:55 AM
  2. [SOLVED] Index-Match which does not require alphabetical sort of names
    By FivestarMac in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-07-2015, 12:39 PM
  3. Index Match & Names Range Problem
    By Barking_Mad in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-10-2014, 08:32 AM
  4. [SOLVED] Combining 3 Formulas: Return all Names that Match Criteria Using Index/Match
    By bchilme in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-29-2014, 09:28 AM
  5. INDEX Match Duplicate names displayed problem.
    By Andrewman36 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-05-2014, 11:00 PM
  6. Index Match Match Formula DOESN'T WORK WITH TABLES/TABLE NAMES ??
    By Underling in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2014, 09:01 PM
  7. Vlookup with index match but on sheet names
    By Blake 7 in forum Excel General
    Replies: 3
    Last Post: 02-01-2012, 06:15 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