Filter/ combine data

[Rubystarx]

I have data that was exported to excel then filtered into a list of stores
that have ordered from us over the past year. I now need to match that list
against a list of all stores and thier addresses so that i end up with a list
of addresses for the stores that have ordered from us in the last year. Here
is what it looks like

Workbook1 Workbook2

Store # Store # Address
001 001 123 Main Street
003 002 465 Main Street
007 003 1 State Street


How do I take the information in workbook 2 and make it only show the stores
listed in workbook 1 without going through and hiding each store that I do
not want to see. if i hide them individually it will take all day. I am
working with Excel 2007. Please help.

 

[Max]

Why not use index/match to retrieve it directly?

Assuming both Book1.xls and Book2.xls are open at the same time,
with data assumed within Sheet1 in cols A and B, from row2 down
(the source Book2.xls contains Store#'s in col A and Addresses in col B)

In Book1,
Store#'s are assumed in A2 down
Put in B2:
=INDEX([Book2]Sheet1!$B:$B,MATCH(A2,[Book2]Sheet1!$A:$A,0))
Copy down to retrieve the addresses from Book2's col B

In the above, unmatched cases will return "ugly" #N/As
If you want it to return neat looking blanks: "" instead
for any unmatched cases, use this in B2, copy down:
=IF(ISNA(MATCH(A2,[Book2]Sheet1!$A:$A,0)),"",
INDEX([Book2]Sheet1!$B:$B,MATCH(A2,[Book2]Sheet1!$A:$A,0)))