Is it possible to lookup multiple values and return multiple rows of results?

**justin11** · 05-20-2013, 11:32 AM

Hi, new to the forums here but have a question for which I can't seem to find a suitable answer.

I have two sets of data I'd like to compare to one another to find multiple values. Dataset A is several columns long and includes about 5000 customer addresses, with separate columns for address, city, state, and zip code, among other identifiers. I need to cross-reference these addresses with Dataset B, which is one column of about 500 zip codes.

Basically, I'm trying to return the rows of customers whose zip codes do not fall into that list of 500. That is, out of the 5000 customers, I'd like to get a list of the ones who do not have these zip codes. So let's say 100 of them do not have one of the 500 zip codes. How do I isolate the entire rows of data for all 100 of these people? Alternatively, how can I get a list of the 900 who DO match?

I can't seem to find exactly how to use vlookup for to both find multiple zip codes and return multiple customer addresses. I also attempted writing a macro based off stuff I've learned on this forum, but it only returns the first row (that is, one customer per zip code even though each zip code may have 300 customers attached to it). The macro route has been more successful, as I at least got to a point where it was finding one corresponding address. No luck at all with vlookup.

Help?!

**Jakobshavn** · 05-20-2013, 12:00 PM

See the attached workbook

**FDibbins** · 05-20-2013, 12:02 PM

Hi and welcome to the forum

Are you saying that you want to ID those rows in the 5000-list that have zip codes not matching the zip codes in the 500-list?

If so, then the vlookup should do this for you
Assuming the zip code is in column A on both sheets, try something like this in the 5000-list sheet...

=vlookup(A2,500-list!A:A,1,false) this will pull out all the matches from the 500-list sheet
you could then do something like this, to eliminate the error messages...
=iferror(vlookup(A2,500-list!A:A,1,false),"Not Found")

If that doesnt work for you, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.