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!
Bookmarks