Hello all. Thanks for taking the time to look at this.
I need to do a dependent lookup. On one sheet, I have four columns: Name, Date, Election Type, Election Amount. The first two of these columns are populated. Our goal is to populate the latter two columns.
On the second sheet, we have: Name, Election Type, Election Amount, and Effective Date. Now, in the original document, I'm working with about 100,000 rows in the first sheet, and even more on the second. (Yeah, there are likely better programs for this, but this is what we've got). I need to populate the empty rows of the first sheet by looking up the data on the second by name, and then finding the data that is closest to AND earlier than the date given on the first sheet.
So, for example, if the "Date" on the first sheet is 10/14/14, we need to look at the second sheet, first find the appropriate name, and then find the data with the closest "Effective Date" without going over 10/14/14.
I have attached a small example, complete with how the data should look when it's correct. For simplicity's sake, there is only one name in the example, but there are thousands in the original. I feel as though this should be somewhat straightforward, but I'm having a heck of time coming up with the appropriate logic here. Anyone have any ideas? Thanks in advance.
Bookmarks