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?
 
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
 
JoelAuthor Commented:
Thank you for the quick response! This is exactly what I was looking for. A+!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.