Complicated Excel Lookup Formula

Isn't there a way for you to simulate my manually hitting the enter key at the end of each formula?

simulate manually calculate may not solve the speed problem.

play with sql may run faster.

create temp table aa as
select rowid old_rowid,* from original_keys_school_first_lastnames;
//select * from aa;
cli_one_dim~temp.aa~1;
create temp table bb as
select old_rowid,regreplace('\s*(&|and|\s)\s*','|',group_concat(数量,'|')) keywords from aaunion group by old_rowid;
select * from bb;
select regexp2(keywords,`Owner Name`),* from bb left join original_keys_school_fAOBS_Sample_database b on regexp(bb.keywords,`Owner Name`);

mtarler I am sorry for being a challenge to deal with. Lots of family issues, including my wife who has been sick for 3 years.  1) AOBS is NOT our database: it's a database of homeowners we have scraped from the Blockshopper.com website.  My goal is to match names from Blockshopper to names I am able to obtain from various sources.  Sometimes, I get names of husbands and wives; other times, I get student names. When I get a student's name, only his or her last name matters because the student's name is rarely listed on Blockshopper.   If the student has an unusual last name, we can often find the right match. But if the student's last name is "Smith," all bets are off. When we get the parents' names, we have a better chance of finding the right match on Blockshopper, because some version of both names is usually listed on the property title.  Sometimes, I try to find matches based on last names; other times based on both names.

I only understand parts of your formulas, but enough for me to get decent results. Your formulas with multiple names are amazing: they don't get as many results, but the results are unbelievably accurate because you're matching a few names.  I will play with what you just sent me and see how it does. I'll be back in touch soon (hopefully) with an update.

Mark B.

marcwithak629 so you say "... I am trying to add more names to our database" but then you say "Whenever there's a match, I'm bringing that data to the #1 Formulas worksheet".  That seems backwards to me since isn't AOBS the database that you are trying to fill in?  Regardless, in the attached I added a tab called Lookup Tool, as an example of what I was talking about.  I added a column on the #1 formulas worksheet called skip# just so you insert a number or letter or anything to skip it for later or maybe mark it as 'done' after going through all the 'blanks' you can then go back and review the 'skips'.  Note in this example I include any line from the AOBS that finds at least 1 of the last names from the #1 sheet.  This lookup could get more fancy and create "scoring" based on how many and which items match and then sort the results based on that scoring.  This technique will also be MUCH faster and efficient as excel only has to look up 1 item at a time since you will only be looking at 1 at a time that should be fine.

Note I also formatted both tables as tables which makes things more efficient and easier to sort or filter but technically not required for this concept to work.  And formatted as table the look-up in that table creates a !spill as that is not allowed inside tables but my proposal is to not use that lookup in the table.  

marcwithak629  so answer to #1 is no but if we make the formula more efficient it will help.  so on that point, here is a variation might be better:

=LET(in, 'AOBS Sample'!$B$5:$G$40,
key, IF(TRIM(G2)="",NA(),  G2),
inC, " "&SUBSTITUTE(INDEX(in, , 1),","," ")&" ",
pass, ISNUMBER(SEARCH(" "&key&" ",inC)),
fset, FILTER(in,pass,0),
out,IFERROR(INDEX(fset,2,),INDEX(fset,1,)),
out
)

as for the rest I have some questions and such.  and the file you attached has formulas that point to other data sources and the data source tab (AOBS Sample) doesn't have any matches for the sample search data.

as for being able to search for multiple names in the same formula we could definitely  add some if conditions inside the filter or as part of the pass but i need to know more to know how.  For example the last name must always match, the state if present must match, then what to do about first name? Like if it matches any results then only return those?  basically the if present then must match is much easier to deal with than if present and if matches then ... 

on yet another note, it sounds like you are trying create a sublist to help you decide which actual data line is correct reference.  Can i suggest maybe a different format.   For example I can image having tab A the new incoming data and tab B the directory data set and then in tab C you could have it automatically show the first line from tab A that is missing x/y/z info (or x/y/z is set to a certain value, but I'll mention that more below).  Then under that line would be all the potential matches from tab B.  Once you determine which is the right match you copy and paste that info onto tab A which then means that line is no longer blank so it shows the next line with missing info.  basically tab C is then a tool to go through and update tab A.  Now let's say you get a line you just aren't sure about and want to skip, may you enter xxxx into the field we are checking so it will 'skip' that row but later you can change that setting I mentioned above from 'blank' to 'xxxx' to go back and re-review the questionable ones.

and one step further, I would recommend adding an unique index value to the directory tab and then adding the corresponding index value onto tab A instead of copying all the information over.  Having the unique id will make any future referencing easy and you don't bloat your workbook unnecessarily.