Filtering data in three columns based on the content of two other columns

Hey all,

I'm curious on the most efficient way to filter data in three columns based on the contents of two other columns. For example, let's say you have a list of 10,000 first name, last name, and address in column A,B,C and you want to filter them based on the first name and last name in column D and E. So basically where the first name and last name of column D and E matches the first name and last name of column A and B, you want to only retain the information of column A, B, and C and get rid of all other cells in A, B, and C. Is the most effective way to do this a vlookup or some other more efficient way?

Thanks for any response.

You could concatenate both sets of First Name and Last Name with a space separating First and Last Name then use INDEX with MATCH to get the addresses of the shorter columns.
So if column A First Names are A2:A10001, column B Last Names are B2:B10001, column C Addresses are C2:C10001, column D First Names are D2:D500, column E Last Names are E2:E500 then in G2 filled down to G500

Please Login or Register  to view this content.

In H2 filled down to H10001

Please Login or Register  to view this content.

In F2 filled down to F500

Please Login or Register  to view this content.

Then the wanted names and addresses are in D2:F500.

Thanks for reply.

I tried what you said but I get #VALUE!

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

Try

Please Login or Register  to view this content.

in H1 filled down to H200.

Thanks that works, but that only pulls the middle initials (column c) to a new column. What if I want the matching first and last names in the new column as well? If I try this:

Please Login or Register  to view this content.

I get a #REF! error.

Please Login or Register  to view this content.

gives the #REF! error because A$1:C$354 is a 3 column range and in the INDEX function you have not included the argument that specifies which of the 3 columns is to be looked up.
Try

Please Login or Register  to view this content.

F1 already has Last Name Space First Name, then INDEX(C$1:C$354,MATCH(F1,G$1:G$354,0)) returns the appropriate Initial.