Forum Discussion
Solved
Complicated Excel Lookup Formula
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 ...
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.
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)
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)
- I got it to work! 🙂 I must have messed it up when I was copying and pasting. Thanks a ton!
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.
- 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.
- 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."