VLOOKUP


B

BStacy

I have a spreadsheet with a worksheet with several thousand physician names,
addresses, etc.

In another worksheet in the same file I have a list of about 200 zip codes.

I need to pull all of the physicians from worksheet 1 that have office
addresses in the zip codes in worksheet 2.

I tried using VLOOKUP but I'm doing something wrong and it's not working.
Can someone tell me if VLOOKUP is the best function to use and how to use it
for this situation?
 
Ad

Advertisements

F

Francis

Is you zip codes in the full addresses? if yes, traditional Vlookup can't
get the result.

Post your formula and a small sample of your data in sheet1 and sheet2

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
B

BStacy

No, the Zip Code is separate from the address.

The fields in Worksheet 1 look like:

FirstName, LastName, Address1, Address2, City, State, Zip, Phone, Specialty

Worksheet 2 only has a Zip Code field.
 
S

Sheeloo

Assuming Col G contains the zip code in worksheet1, enter this in the first
available column..
=IF(ISNA(VLOOKUP(G1,Sheet2!A:A,1,FALSE)),"","Pick")

You can then filter on Pick... to get the addresses you want...
 
F

Francis

I am assuming your zip codes in col A, row 2 of sheet2
try this in B2 of sheet2
=INDEX(Sheet2!A1:A10,MATCH(A2,Sheet2!$G$1:$G$10,0))
this will pull the first name
in C2 of sheet2
=INDEX(Sheet2!B1:B10,MATCH(A2,Sheet2!$G$1:$G$10,0))
this will pull the last name

Select B2 and C2, copy down
yoy can use whole column as your reference if you want to.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
B

BStacy

This works perfectly but can you tell me why this works so that I can use
this to fit other situations?
 
Ad

Advertisements

S

Sheeloo

I will try...
=IF(ISNA(VLOOKUP(G1,Sheet2!A:A,1,FALSE)),"","Pick")

VLOOKUP(G1,Sheet2!A:A,1,FALSE)
takes the value in G1, goes to Col A on Sheet2 and tries to find a match...
if it does it returns the value from Col 1 in the range Sheet2!A:A and
returns the value there (so if you had the range as Sheet2!A:B and 2 instead
of 1, you would have got the value from Col B)
If no match is found you get #N/A error...
ISNA() checks for the return value... if it is TRUE (i.e. you got no
macth), IF returns nothing ("") and returns PICK if a match is found...
instead of PICK you can have the whole VLOOKUP there to get the value back...

Hope I did not confuse you...

See http://www.contextures.com/xlFunctions02.html for an excellent article...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top