View Single Post
  #1  
Old September 30th, 2010, 02:41 AM
johnmerlino johnmerlino is offline
Member
 
First recorded activity by OfficeFrustration: Sep 2010
Posts: 1
Default Filtering data in three columns based on the content of two other columns

Hey all,

Basically, I have a last, first, and initial in column A, B, C respectively - a list of 1000. Then I have last, first in column D, E respectively - a list of 200. The idea is to get the first and last name that match between A/B and D/E into their own column. So in Column F, I use this:
Code:
=D1&" "&E1
I then manually drag the bottom right corner of cell down 200 rows so that formula concatenates the first and last name for all 200 rows.

Then in Column G, I use this:
Code:
=A1&" "&B1
I then manually drag the bottom right corner of cell down 1000 rows so that formula concatenates the first and last name for all 1000 rows - hence all first and last names of column A and B are now concatenated in column G.

Now I want to compare column F and G and whichever first and last names match, then I want the matching first name, last name, and middle initial of column A, B, C to be in their own new columns.
So in column H, I do this:
Code:
=INDEX($A$1:$C$1000,MATCH($F1:$F200:$G1:$G1000,0))
This line of code, rather than returning the matching criteria in column H, gives a #VALUE! in the very first cell.

Thanks for any response.