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!