How to select particular rows in column

[pralav]

Hi

I have two sheets. In sheet 1 I have two columns. Column A has 500
names and column b has tel numbers but not for all of the names. So
some names donot have any values in column B.

In sheet 2 I have the same two columns. But here column A has only a
subset of the names in sheet 1 i.e., around 350 names, and all these
names are present in that name list in sheet 1.

I want to import the tel numbers from sheet 1 to names in sheet 2. I
will greatly appreciate any help regarding this. I need to know how we
can write a formula to match the names and import their corresponding
values.

Thank you very much
Pralav

 

[Ron Coderre]

On Sheet2 (and if the phone numbers are numeric):
Insert this formula in B2 (assuming there's a column heading) and cop
down:

=IF(VLOOKUP(A2,Sheet1!$A$1:$B$500,2,0),VLOOKUP(A2,Sheet1!$A$1:$B$500,2,0),"")

Actually, this formula works either way (text or numeric):

=IF(VLOOKUP(A2,Sheet1!$A$1:$B$500,2,0)=0,"",VLOOKUP(A2,Sheet1!$A$1:$B$500,2,0))

Does that help?

Ro

 

[pralav]

Thanks a lot ron.. I will try it and let you know. Greatly appreciate
it. Could you also explain that formula if possible. Thank you

 

[Peo Sjoblom]

One way would be to use vlookup

=VLOOKUP(cell_with_name_from_A,Sheet2!$A$2:$B$350,2,0)

assume the names start in A2 in sheet1

=VLOOKUP(A2,Sheet2!$A$2:$B$350,2,0)

then copy down

to get all blank numbers first you could select both columns and sort
ascending on B that will put all empty numbers on the top, then put in the
above formula in the first empty cell and copy down, it might be amended
with

=IF(ISNUMBER(MATCH(A2,Sheet2!,A$2:$A$350,0)),VLOOKUP(A2,Sheet2!$A$2:$B$350,2,0),"")

--
Regards,

Peo Sjoblom

(No private emails please)

 

[pralav]

It is not working. May be I am doing something wrong. Is there anyway
can send you the sheet so you can actually take a look at it.
simplified the explanation by saying tel numbers. The actuall data
have is set of classes. I have around 667 in column a sheet 1, so
changed the 500 number to 667. But it doesnt look like it is comparin
properly. Please let me know. Thank yo

 

[pralav]

Thanks a lot Ron and Peo. It worked!!