Tricky problem using vlookup.

[Barcode]

I have membership data stored in a sheet and I need to extract data for some members to a second sheet.

Using vlookup I can extract the data without any problems. BUT, some members have 2 rows of data where the data is identical except the first row has a mobile phone number in column 8 and the second row has a home phone number in column 8.

How can I extract the home phone number from the second row and have it placed on the same row in the second sheet that I am extracting data to?

The second sheet will look like this:
Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Member#[/TD]
[TD="align: center"]Fname[/TD]
[TD="align: center"]Surname[/TD]
[TD="align: center"]Email[/TD]
[TD="align: center"]Mobile #[/TD]
[TD="align: center"]Home Phone[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]2345[/TD]
[TD="align: center"]Bill[/TD]
[TD="align: center"]Bloogs[/TD]
[TD="align: center"]bb@xxx.com[/TD]
[TD="align: center"]0400123456[/TD]
[TD="align: center"]0312345678[/TD]

</tbody>

​

A modded part of the Membership sheet showing a member with 2 rows of data (Home and Mobile phone):
Excel 2016 (Windows) 64 bit

	B	C	D	E	F	G	H	I	J	K	L
Bill	Bloogs	123 First Street	Black Stump	VIC	312345678	Home	bb@xxx.com
Bill	Bloogs	123 First Street	Black Stump	VIC	400123456	Mobile	bb@xx.com
KENNETH	xxxxxx	xxxxxxxxxxxxx	xxxxxx	xxxxxx	VIC	423234234	Mobile
WAYNE	xxxxxx	xxxxxxxxxxxxx	xxxxxx	xxxxxx	VIC	434345345	Mobile

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]15[/TD]
[TD="align: right"]2345[/TD]

[TD="align: right"][/TD]

[TD="align: right"]3551[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]2345[/TD]

[TD="align: right"][/TD]

[TD="align: right"]3551[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]3600[/TD]

[TD="align: right"]3555[/TD]

[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]3601[/TD]

[TD="align: right"]3555[/TD]

[TD="align: right"][/TD]

</tbody>

TIA
Bob

 

[sandy666]

try PowerQuery (Get&Transform)
- remove unnecessary columns
- Pivot PhoneType column by PhoneNumber column with Don't Aggregate option

 

[RoryA]

You could use a lookup formula like this:

=LOOKUP(2,1/(B15:B18=A2)/(K15:K18="Home"),J15:J18)

and just change Home to Mobile as needed.

 

[AlanY]

one way

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1	2345	Bill	Bloogs	123 First Street		Black Stump	VIC	3551	312345678	Home	bb@xxx.com
2	2345	Bill	Bloogs	123 First Street		Black Stump	VIC	3551	400123456	Mobile	bb@xx.com
3	3600	KENNETH	xxxxxx	xxxxxxxxxxxxx	xxxxxx	xxxxxx	VIC	3555	423234234	Mobile
4	3601	WAYNE	xxxxxx	xxxxxxxxxxxxx	xxxxxx	xxxxxx	VIC	3555	434345345	Mobile
Sheet2

Excel 2013/2016
	A	B	C	D	E	F
1	Member#	Fname	Surname	Email	Mobile #	Home Phone
2	2345	Bill	Bloogs	bb@xxx.com	400123456	312345678
Sheet1

Cell Formulas
Range	Formula
E2	{=INDEX(Sheet2!$I:$I,MATCH(Sheet1!$A2&TRIM(LEFT(SUBSTITUTE(E$1," ",REPT(" ",500)),500)),Sheet2!$A:$A&Sheet2!$J:$J,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Barcode]

Thanks for the replies sandy, Rory and Alan. much appreciated.

There's a lot I don't understand there but I'll play around with it and see how I go.

 

[Barcode]

I've tried for a few hours to implement the 3 options above but to no avail. I can use vlookup with no problems but I just cannot understand the options above.


Is it ok for one of you to show me how it's done? If so I will attach a desensitized copy of the members file.


TIA
Bob

 

[Barcode]

Actually cancel that above request. I'd like to know how to do it for future use but it is not needed now. It took less than an hour to do the job manually. I expected it to take much longer.

Thank you fellas for your help, it was very much appreciated. I've been coming here for years seeking help and I've always got a result. It definitely is a gold mine for Excel users.

 

[sandy666]

look at PQ-PivotColumn

 

[Barcode]

I had a look at it but it was too much to understand in 1 viewing. I'll have to throw it up on the large screen and use the pause button (a lot )


Thanks Sandy

 

[sandy666]

You are welcome

Have a nice day