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.
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.
=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 200with 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),