Lookup based on two variables and return a third

[Mark]

Hello,

I have an application in which I'd like to match up data from two large
spreadsheets, lists of addresses. One of the lookup variables, a customer
code is in a single column, column A, but the second is in one of 5 columns.
There are several addresses for a given customer code, so I need a formula to
meet both criteria, and then return a value called the address code, which is
in a different column than those listed above.

I have been experimenting with VLOOKUP, INDEX, and MATCH functions, but the
fact that the address I need to match up to can be in any one of five columns
is making it difficult to tell excel what value to return.

Any help would be appreciated.

 

[Mark]

An example may help;

A B C D
E 1 Cust Code Addr Code AddrLine1
AddrLine2 AddrLine3
2 205 000001 201 1st St Anywhere, TX 31204
3 164 000002 Product Dept 1120 Last St Nowhere,
CA 00000
4 221 000004 Dock #2 3111 Rodeo Dr
Everywhere, MI 11111

 

[dan dungan]

Do both spreadsheets contain the customer code?

I wasn't quite able to understand your example, but it appears there
are
three address fields that do not always contain an address--sometime
it could be
a company name?

What data, specifically, are you attempting to match up.

I'm wondering if you could match up the data, what would you do with
the result?

 

[Mark]

Dan,

Thanks for your response. Only one of my two spreadsheets has the address
code. What I'm trying to do is populate the address code to the spreadsheet
that does not have it, by looking it up by customer code and address from the
sheet that does have it. The reason for doing this is that we've manually
cleaned up, (always put the street address in the line 1 column), the address
fields on the sheet that does not have the address code, but now need to add
it. The challenging part is the fact that sheet with the address code does
not have the data in the address columns cleaned up, so the street address
could be in one of three columns.

So, I need to first match up the customer code, then match up the street
address, (as a given customer code may have multiple locations, or
addresses). The difficult part comes in where I have to look in three
columns for the street address to make sure I find it, because, as you noted,
the first address column is sometimes used as an additional line for the
company name.