Filtering a LONG list to entries in a shorter list

[excel_novice_UK]

hi,

I'm hoping someone has come across this type of problem before and could help. I'm getting depserate!

Basically I have a list of email aliases for my company, approx 10,000 entries, mapping the alias to an email address. Two columns, one the alias firstname.surname, second column being the full email address.

I have a second list, a subset of these aliases, approx 1200 of them, that are users of a particular web app. All i have in this list though, is the firstname.surname bit.

What I need to do is compare the lists, and where the aliases match I need to pull out the corresponding full email address from the long list, so I have 1200 aliases in the shorter list, with the full email address mapped to it.

Is this possible in excel? i've tried all sorts of LOOKUP functions, and MATCH functions, but none seem to work. MATCH will give me the corresponding row number in the long list, but then it seems to offset itself every row it goes down, so the values are 1 out for every row. First row is good, second row -1, third row -3, fourth row -4 etc.

I don't want to have to manually go through 1200 entries and match them against a 10,000-entry list. Hopefully there's a quick way in excel to filter the long list down to the entries specified in the short list? Can anyone please help?

Many thanks in advance!
Lee

[GuruWannaB]

Can you provide an example file?

[werfnort]

A vlookup formula should do the trick!

In the smaller list part, create the formula =VLOOKUP(a1,Sheet1!$A$1:$B$10000,2,FALSE)

Or something similar.
A1 will be the value it will look for in the huge list.

Sheet1!$A$1:$B$10000 - will be the huge list of data, including both the lookup column and the column where the information is that you want.

2 - is the column number of where the information is that you want.

False - will say that it needs to be an exact match between the lookup value and the lookup column.

Hope this helps!

[excel_novice_UK]

Werf - you are a star!!

Thank you so much! I've scratched my head with this for the whol afternoon and in five minutes you solve it for me!!

I must've tried every other of the functions in the lookup subset there, but never got down to vlookup!

Thanks again, that's exactly what I needed.