matching columns based on specific criteria

**abuchan** · 06-30-2010, 04:21 PM

I am attaching a sample worksheet. Sheet 1 has a list of 'parcel numbers' in Column A. I need the appropriate street address for every parcel in Column A. On sheet 2 I have a master list of parcel numbers with their respective street addresses.The parcel numbers on Sheet 1 are mixed into the list on column 2 however my 'real' list has about 2000 on sheet 1 which comes from a list of about 10,000 on sheet 2 which is too much to do by hand. I am hoping for a macro to place the appropriate address from sheet 2 next to the parcel number on sheet 1.

Many thanks.

**NBVC** · 06-30-2010, 04:28 PM

Try this formula in Column B:

=IF(ISNUMBER(MATCH(TRIM(A1),Sheet2!A:A,0)),VLOOKUP(TRIM(A1),Sheet2!A:B,2,0),"")

copied down.

you will get blanks if code doesn't exist

**abuchan** · 06-30-2010, 04:47 PM

Thank you for your quick response. I tried it and it gave me the first street number and stopped. Could that be because the street address on Sheet 2 is in columns B, C, D, and E?

Thank you again.

**NBVC** · 06-30-2010, 04:51 PM

I thought you only wanted column B...

Try in B1:

=IF(ISNUMBER(MATCH(TRIM($A1),Sheet2!$A:$A,0)),VLOOKUP(TRIM($A1),Sheet2!$A:$E,COLUMNS($A$1:B$1),0),"")

copied across and down.

Note: You have a blank column C in your example.

**abuchan** · 06-30-2010, 04:58 PM

My errorr regarding column C. It is used for street direction and the sample I chose didn't include any. I can't seem to get past the first street number. I must be doing something incorrect but can't figure what. I will continue trying, thank you.