Return value of a cell N rows away based on Index Match of 2 other cells.

Excel 2010, I'm reformatting a large set of data for an ERP implementation.

Initially we had a customer record that was strictly for reporting purposes, but changes have made it so the entity no longer is strictly for reporting, but needs to have a full data set (such as a valid address). This record type is actually just a mirror of one of the existing sub-records.

I've attached a demo sheet with sample data as I have it, and data as I need it.

For about 50% of the data, its simple, I can just poll the address from the cell below. For the rest of the data, I have to match the row to be changed by the ZIP code cell, and then return the Street Address of the matched ZIP code into the dummy street address.

For example, in the data set:
D2 needs to equal D3, because G2 and G3 match.
D4 needs to equal D6, because G4 and G6 match.
D8 needs to match D9, because G8 and G9 match.

The trouble I'm having is that because I cannot reliably predict how many rows below the cell I need to change will be, I'm not sure how to write the formula to pull the value from column D that matches the row with the same ZIP.

To make it a bit easier if needed, the ID column (column A), does have a consistent set of the first 5 characters (=left($A#,5)) will be the same between the Type1 row and all subsequent Type2 rows below it.
Experts-Exchange-Demo.xlsx
JoelAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
I have highlighted the formula in I Column to do what you are looking for..

formula used..

=LOOKUP(2,1/((LEFT($A$2:$A$9,5)=LEFT(A2,5))*($G$2:$G$9=G2)),$D$2:$D$9)

Open in new window


Your workbook...

Saurabh...
Experts-Exchange-Demo.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JoelAuthor Commented:
Thank you for the quick response! This is exactly what I was looking for. A+!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.