function/formula help

[suki2shoes]

Hi

really appreciate if someone could help me

sheet1 has a list of mobile telephone numbers. ( with some details
regarding each number, name, status etc, but none of the details are
filled in)

sheet 2 has all the mobile numbers plus all the details of the people.

what i need to do is, get sheet 1 to look up the mobile number in sheet
2 and take the information (name, status etc) from sheet 2 and put in
sheet.

please help

thanks

 

[Bob Phillips]

B1: = VLOOKUP(A1,Sheet2!$A$1:$H$1000,2,False)

C1: = VLOOKUP(A1,Sheet2!$A$1:$H$1000,3,False)

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[nijunge]

Use vlookup

be sure that the list of number with the data is sorted assending

good idea is to name the list

it will look something like =vlookup(a3;list;2)

a3 being the ref list being the list and 2 being the row number in th
list counted from left

works like a sharm as long as the lists are under 500

 

[suki2shoes]

does that code match up the mobile numbers ?

i'm a real novice in functions, finding it kinda hard

 

[Jon Quixley]

Hi Suki2shoes

What you need is a lookup table. The data you have in sheet one reall
needs to be arranged in numerical order to start with. To do thi
highlight one of the numbers in the column of numbers and selec
Data/Sort and select the numbers in ascending order. You should do th
same thing with sheet 2 sorting them according to the phone number. Yo
now should have two sheets both ordered according to a limited number o
phone numbers on sheet 1 and a full set of numbers on sheet 2.

The layout of the numbers in sheets 1 and 2 have to be identical - b
that I mean that the number 01753688499 and 0 1753688499 (eg) are no
the same and the program won't find a match

The next thing is to set out sheet 1 with the categories you want pu
populate your sheet with - something like
ColA: Mobile Number (already set up and in numerical order)
ColB: First Name
ColC: Last Name
ColD: Job Title
etc (obviously these catagories are going to be already in sheet 2)

In sheet 1 colB row2 (Row 1 contains the titles), enter the formula:
=Vlookup(a2,'sheet2'!$a$2:$zz$200,2) You will find this easier if yo
point to each step in the formula with the mouse (I assume you ar
familiar with constructing formulae). What this is doing is looking a
the number in cell A2 on sheet1 and going off to sheet2 to find th
same number in the first column of the range you have specified - thi
is column a since the range is from col A to ZZ. Having found a matc
in column A, the formula returns the data in the second column adjacen
to the matching number - this is whrere the number 2 at the end of th
formula comes in.
To return the information in the third column, this number needs to b
changed to 3, for the 4th to 4 etc.

Have a look at the attached (zipped) spreadsheet

Jo

+-------------------------------------------------------------------
|Filename: suki2shoes.zip
|Download: http://www.excelforum.com/attachment.php?postid=3743
+-------------------------------------------------------------------