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