SOLVED

Complicated Excel Lookup Formula

Contributor

Hi,

 

I'm using the formula below to find people who match the name in cell G407. The "Moraga" worksheet in the Lafayette file has cells with a lot of text, including text that matches the name in G407. The formula below gives me the first instance of a match. I want to change the formula below to find the 2nd match in the Moraga worksheet.  Any help would be greatly appreciated.

 

=VLOOKUP("*"&G407&"*",'[Lafayette.xlsx]Moraga'!$C:$P,3,FALSE)

 

A cell in the Moraga worksheet might look like this: smith trust, john smith, candy and john smith trustee.  If I am looking for a Smith, this cell would work and I would get the value or text in the corresponding column E.

 

Thanks in advance for your help.

28 Replies
First question is which version of Excel are you using? If you are on Excel 365 then you can use something like =IFERROR(INDEX(FILTER('[Lafayette.xlsx]Moraga'!$C:$P,IFERROR(SEARCH(G407,'[Lafayette.xlsx]Moraga'!$C:$C),FALSE),0),2),"NONE")
if you don't have Excel365 then maybe something like:
=VLOOKUP("*"&G407&"*",OFFSET('[Lafayette.xlsx]Moraga'!$C1:$P10000,MATCH("*"&G407&"*",'[Lafayette.xlsx]Moraga'!$C1:$C10000,0)),3,FALSE)
(NOTE: since I don't have those sheets these are untested so I apologize if there is a typo in there but at least I hope they give you an idea of how it might work)
best response confirmed by marcwithak629 (Contributor)
Solution

@mtarler I have 365.  I have it almost working now.  Sometimes, the formula returns information from the first cell/column ("C") and other times it returns info from the next cell/column ("D").  I just figured out when it does this: if there is not another instance of the G407 term, the formula gives me the number in cell D - it shifts to the right, instead of down. It would be much better if it can give me the result from the only time the name can be found. If you can fix this, I will be grateful.

 

IGNORE my other messages.

I tried the first formula and it found the first instance of the search term. My goal is to have a formula that can find subsequent instances of the search term. The search terms are names, and there are many people with the same last name. My plan is to duplicate the formula, but change which instance a search retrieves. Then, I can decide which one to use. Hope I'm making sense.
I tried your second option and I received the following error message:

"you've entered too few arguments for this function."
I got it to work! I must have messed it up when I was copying and pasting. Thanks a ton!
Since you have 365 are you using the FILTER version I suggested? If you want the 1st/only occurrence to return if there is only 1 then maybe it would be best to wrap this in a LET() statement (again untested) something like this:
=LET(in, '[Lafayette.xlsx]Moraga'!$C:$P,
inC, INDEX(in, , 1),
fset, FILTER(in,IFERROR(SEARCH(G407,inC),FALSE),0),
IFERROR(INDEX(fset,2,),fset)
)
so "in" is the full table and "inC" is the first column of "in". Then "fset" is the filtered set based on searching for G407 in "inC". Last is try to Index 2nd row but if that fails then just return the 1 row found.
I'm sticking with your very first formula with one additional request. Is there a way for the search to only recognize whole words? I want to indicate that the word would have a space before and after it, but I'm not sure how to write that. For example, I'm looking for G407, but only when it's a standalone word (not part of a longer word). How can I do that? Thanks again.
Actually, I have one more question. The response to your original formula above includes additional information, which is hugely helpful. This info comes from information on the Moraga spreadsheet that is adjacent to the cell that contains the information that I'm most interested in. I labeled the first few columns below so you can see what I'm saying. How did you make this happen?
C D E F H
Dolores L Austin 21 freitas drive FALSE Trust FALSE 21 freitas drive 21 freitas drive Oct. 24 200

@marcwithak629 

with respect to only whole words that creates a problem because if you just add a space before and after then if that word is the 1st or last word in the cell then it probably won't have a space. it would be so much easier if i had a sheet to play so I create a mini sheet (attached) created this LET() formula:

=LET(in, '[Lafayette.xlsx]Moraga'!$C:$P,
key, G407,
inC, INDEX(in, , 1),
inCloc, IFERROR(SEARCH(key,inC),-1),
keyfinds, TRIM(SWITCH(inCloc,-1,FALSE,1,LEFT(inC,LEN(key)+1),MID(inC,inCloc-1,LEN(key)+2))),
pass, key=keyfinds,
fset, FILTER(in,pass,0),
out,IFERROR(INDEX(fset,2,),INDEX(fset,1,)),
out
)


As for how it returns the additional cells is that the formula is using
... FILTER('[Lafayette.xlsx]Moraga'!$C:$P, ...
so it pulls columns C:P and filters for only the rows that meet the criteria that follows in:
...,IFERROR(SEARCH(G407,'[Lafayette.xlsx]Moraga'!$C:$C),FALSE)...
which basically searched each value in column C for the value in G407 and returns true if found or false if not found.  in the above LET() version i do a bit more to first determine which rows "pass" or not and then use that "pass" variable to filter the "in"put range.

 

BTW, in that LET() statement you define the area to search next to "in", the search key you want to look up next to "key" and it assumes you are using the 1st column based on the inC (i.e. in column) being set to 1 in the line: inC, INDEX(in, , 1),

@mtarler I'm feeling SUPER guilty about your time, but I'm going to update you a sample spreadsheet so you can see what the Moraga file looks like. I (actually, YOU) have it working pretty well, with one glaring exception. When there is only 1 record in the Moraga file that matches the name, the formula returns a number (from the adjacent cell). That's okay because I then sorted the spreadsheet and changed the formula for all of the numbers from 2 to 1. It returned the correct information for the homeowner, BUT it did not return all of the other information in the adjacent cells. I'm guessing that somehow the second iteration triggers everything else (when it's a 2, not a 1). Is there a way to get the 1 to have the same impact?  Just to clarify, if the search term is Poggetto and there isn't another Poggetto in the file, the formula will return 38 because it's in the cell adjacent to the cell that includes Poggetto.

 

Also, I'm having trouble following your explanation about how to only get standalone versions of the search term, but I will try again when I'm more alert.  Thanks so much for all of your help.  When we're finally done here, I'd love to send you a thank you gift.

You did it! I tried your new formula and I think it works!!!! I'll let you know for sure later. Thanks so MUCH.

@mtarler Hi Matt, I just saw your profile - that's how I know your name.  I've attached the same spreadsheet, but I added two rows at the bottom (from the Moraga spreadsheet). Your formula was perfect for one of the names, but it didn't find the other one.   I hope this helps.  Honestly, I don't understand how your formula really works so I was hesitant to play with it.

 

Thanks,

 

Mark

@marcwithak629 I can't find where you pasted the formula into that sample sheet but I think I know the problem.
on line 15 is the name value: "Richard J Choo, May Y CHOO"
the formula searches and finds the FIRST instance of Choo and checks if a) it is at the start or end of the field and has a 1 space on the opposite side OR b) it is in the middle somewhere with a space on BOTH sides. In this case there is a "," comma after the name. NOT a case I took into account.  

I added that specific case into the logic.  Are there other special cases???

@mtarler I haven't had a chance to check out your file, but I created a new spreadsheet with all of the necessary info, if that helps. You can see my brief comments on each of the two worksheets.  I think we're pretty close and you're a Saint!

@mtarler I am having trouble understanding how I should change my most recent formula (prior to your adding the comma logic).  I'm attaching a word document with the current formula and a few brief comments. I'm hoping that you can update the formula to reflect your comma logic.  Then, I will copy and paste it into my spreadsheet.  I really don't understand how all of this works, so I'm just copying and pasting whatever you provide.

@marcwithak629   OK here is an updated formula for you to try using your input range and search cell:

=LET(in, [blockshopperLamorindawithPurchaseDates.xlsx]Moraga!$C:$P,
key, H966,
inC, INDEX(in, , 1),
inCloc, IFERROR(SEARCH(key,inC),-1),
keyfinds, TRIM(SWITCH(inCloc,-1,FALSE,1,LEFT(inC,LEN(key)+1),MID(inC,inCloc-1,LEN(key)+2))),
pass, IFS(key=keyfinds,TRUE, (key&",")=keyfinds,TRUE,TRUE,FALSE),
fset, FILTER(in,pass,0),
out,IFERROR(INDEX(fset,2,),INDEX(fset,1,)),
out
)

Matt,
You're freaking amazing! Thank you so much!!!
Mark

 

P.S. My dad is also a Cornell alum!

@mtarlerBelow is a small spreadsheet with names that didn't work and my theories. I'm guessing this will be a simple fix. Thanks (yet) again. The system wouldn't let me upload the spreadsheet.

 

Column HMy Theory Blockshopper File                       
Kish Hyphen before & no space after kishAimee Falk-Kish, Jeremy M. Kish     
Nakhle Space before, and comma afterAli Antoun Nakhle, Carol Theokary     
Kung Comma after and no space after last name.Chung Y Kung, Joanna W Kung      
Helder Commas or no space afterGeorge K Helder, Judith M Helder, Tr Helder    
Walloch Commas or no space afterJustin R Walloch, F. Walloch Walloch, Jennifer B. Walloch   
McKillen Hmmm? 2nd McKillen has two spaces.Ross Sakamoto, Angela McKillen, Sakamoto R, Angela McKillen R, Tr Ross 
Volkmer Commas and no space afterWerner G Volkmer, Gisela R Volkmer, Volkmer    
McLeod Comma and no space afterWilliam D McLeod, Mary A McLeod     
             
             
The names here are from column H in our file (the ones we're looking for).  The Blockshopper File is a copy of what is in our Blockshopper file.
None of these searches worked because the name did not have two spaces before and after.  Keep in mind that if the name is the last name in the cell, it won't be followed by a space. Perhaps it can have a space and/or hyphen before or after
     
all of these cases except the first that has a hyphen should have worked. You are using the latest version right? The one with the line that looks like this:
pass, IFS(key=keyfinds,TRUE, (key&",")=keyfinds,TRUE,TRUE,FALSE),
As for figuring out why, you should only consider the 1st instance of the last name (key value) in the cell. The formula finds that 1st instance and grabs it and 1 character before and after it. Then it does a check to see if the result is the same as the name (spaces ignored) or if it matches the key value with a comma after it.
To add other acceptable options you can just add to this list of acceptable. for example to include -name, and -name then use this:
pass, IFS(key=keyfinds,TRUE, (key&",")=keyfinds,TRUE,("-"&key&",")=keyfinds,TRUE,("-"&key)=keyfinds,TRUE,TRUE,FALSE),
as for why the others didn't get found and assuming you are using the version that allows for a comma, I can only make a few guesses. a) this is still looking for 2nd occurrence if that makes a difference (but will return the only occurrence if there aren't more than 1). b) is that comma not the "same" comma? there are many characters that look the same, especially ctrl characters. c) along those same line is it possible it isn't a space before that name but a ctrl character that looks like a space?