You can this with MATCH, INDEX and array formulas. In your example, let's
suppose that the phone numbers are in Sheet2, and you want them in columns F
and G of sheet one. On F1 the header is HOME, on G1 the header is MOBILE.
The names are in Column A of Sheet1, and thay are the exactly the same as
column A of Sheet2.
With all this, you can enter this array formula in F2 of Sheet1:
=INDEX(Sheet2!$C$1:$C$50,MATCH(1,--(Sheet2!$A$1:$A$50=$A2)*--(Sheet2!$B$1:$B$50=F$1),0))
Enter it with CTRL+SHIFT+ENTER. Later, you can copy and paste the formula
across the 50 rows on the F and G columns. It will raise and error in the
ones that haven't got a match, you can either delete that ones, hide with
conditional formating, or modify the formula to capture the error before
giving the result.
Hope this helps,
Miguel.
"SamGB" wrote:
Really have no ideas where to start so ill just explain what i am trying
to do.
I have 1 worksheet listing Peoples names, DOB, age etc. in rows from 1
- 50.
with cell A always being their name.
I then have a second work sheet listing the SAME peoples names followed
by thier Home and Mobile numbers in rows from 1 - 50. HOWEVER when 1
person has a Home AND Mobile number thier name is duplicated, for
example:
Cell A Cell B Cell C
P.Jones HOME 01322 1234124
L.Smith HOME 01454 1231234
L.Smith MOBILE 07841 777777
Now what i need is to merge the two sets of data. Was thinking a macro
that looks for the names listed in the first workbook and then finds
the same names in the second workbook, and depending on what referance
Cell B has in it, it puts the data in cell C into the corosponding cell
in the first workbook. im probally making it more complicated than what
it is but any ideas or suggestions would be a great help. Also if the
macro idea works, what would the code be??
Many thanks
Sam
--
SamGB
------------------------------------------------------------------------
SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263
View this thread: http://www.excelforum.com/showthread...hreadid=545609