function/formula help
 

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


--
suki2shoes
------------------------------------------------------------------------
suki2shoes's Profile: http://www.excelforum.com/member.php...o&userid=25024
View this thread: http://www.excelforum.com/showthread...hreadid=399007

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)


"suki2shoes" wrote
in message ...

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


--
suki2shoes
------------------------------------------------------------------------
suki2shoes's Profile:
http://www.excelforum.com/member.php...o&userid=25024
View this thread: http://www.excelforum.com/showthread...hreadid=399007

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 the
list counted from left

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


--
nijunge
------------------------------------------------------------------------
nijunge's Profile: http://www.excelforum.com/member.php...o&userid=26404
View this thread: http://www.excelforum.com/showthread...hreadid=399007

does that code match up the mobile numbers ?

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


--
suki2shoes
------------------------------------------------------------------------
suki2shoes's Profile: http://www.excelforum.com/member.php...o&userid=25024
View this thread: http://www.excelforum.com/showthread...hreadid=399007

Hi Suki2shoes

What you need is a lookup table. The data you have in sheet one really
needs to be arranged in numerical order to start with. To do this
highlight one of the numbers in the column of numbers and select
Data/Sort and select the numbers in ascending order. You should do the
same thing with sheet 2 sorting them according to the phone number. You
now should have two sheets both ordered according to a limited number of
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 - by
that I mean that the number 01753688499 and 0 1753688499 (eg) are not
the same and the program won't find a match

The next thing is to set out sheet 1 with the categories you want put
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 you
point to each step in the formula with the mouse (I assume you are
familiar with constructing formulae). What this is doing is looking at
the number in cell A2 on sheet1 and going off to sheet2 to find the
same number in the first column of the range you have specified - this
is column a since the range is from col A to ZZ. Having found a match
in column A, the formula returns the data in the second column adjacent
to the matching number - this is whrere the number 2 at the end of the
formula comes in.
To return the information in the third column, this number needs to be
changed to 3, for the 4th to 4 etc.

Have a look at the attached (zipped) spreadsheet

Jon


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

--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=399007