Forum Discussion
Complicated Excel Lookup Formula
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.
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)
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.
- 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
- 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."