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 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.
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)- 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.
