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.
Hello mtarler ,
Do you remember me? You helped me a lot with a few Excel challenges and I am still trying to use the same formulas to extract information from an Excel file. You can look back and see what we did. I have created a new file that hopefully illustrates everything and will make it easier to finish this project. Assuming you can still help me, I'm attaching a new sample file.
The sample file has one worksheet with data that reflects what is on the main spreadsheet (normally 10,000 rows), and another worksheet that reflects what is on the spreadsheet with the homeowner's information. The goal is to use the names from the main spreadsheet to match with homeowners' on the other spreadsheet. It will never be perfect because sometimes we only have a last name and it's "Smith." Here are a few of the remaining problems that I am trying to solve (I wrote "a few" because I cannot remember the other problems and I'm sure something else will come up.):
1) When I fill the formula in column L, it doesn't recalculate the information for a long time. However, if I click on a specific cell, move the cursor to the end of the formula, and then hit enter, it usually populates the new information. Can you add a line of code to the formula so it simulates pressing the enter key?
2) I'm working with a few versions of your formula depending on the following variable: how many names do I have to help me make a match? This ranges from just a last name to as many as two last names and two first names. You've helped me with formulas to match one or two names. Can we add more names without requiring that all of the names match?
3) I spend a lot more time with common last names because I don't have a way to determine which Smith I'm looking for. Without more data, this may be impossible to completely overcome. However, it would save me some time if I can at least restrict the search to a particular city. However, I do NOT want to have to create separate formulas and worksheets for each city. It is incredibly convenient to have the data from all of the cities on the same worksheet.
Those are my main challenges and I hope you can still help me AND you can start to bill me.
Thanks in advance for your amazing work.
Mark Bressler
Lafayette Academy
(925) 951-3794