Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
martelie
 
Posts: n/a
Default vlookup with more than number to be retrieved


I have a list of users and their corresponding mobile numbers. I also
have a bill list with names and the numbers being charged to the
names.
I have used vlookup to retrieve the number being charged to a person.
(a person who i have as having a number)
I use this to check that the correct number is being billed to a user.
However, some users are being billed twice for two different numbers,
but vlookup only shows me the first number they are being charged for.
Is there a way to see both numbers relating to a persons name?

eg

list 1
name number number being billed for(vlookup used for this)
john 12345 56789
paul 54321 54321

list 2
name number
john 56789
john 12345
paul 54321
chris 99999

so john appears twice, but i only see the first number he is being
charged to.. is there not a way for it to be

number being billed for: 56789, 12345 ?

any help or advice would be greatly appreciated.

Martelie


--
martelie
  #2   Report Post  
Roger Govier
 
Posts: n/a
Default

Hi

One way
Insert a new column A and enter the following formula
=B1&" | "&C1 and copy down as far as your data extends.
You will now have unique "names" in your table but your table range will
need to be extended to accommodate the new column

Carry out your lookup by
=VLOOKUP("John | 56789",Table,offset,0)


Regards

Roger Govier



martelie wrote:

I have a list of users and their corresponding mobile numbers. I also
have a bill list with names and the numbers being charged to the
names.
I have used vlookup to retrieve the number being charged to a person.
(a person who i have as having a number)
I use this to check that the correct number is being billed to a user.
However, some users are being billed twice for two different numbers,
but vlookup only shows me the first number they are being charged for.
Is there a way to see both numbers relating to a persons name?

eg

list 1
name number number being billed for(vlookup used for this)
john 12345 56789
paul 54321 54321

list 2
name number
john 56789
john 12345
paul 54321
chris 99999

so john appears twice, but i only see the first number he is being
charged to.. is there not a way for it to be

number being billed for: 56789, 12345 ?

any help or advice would be greatly appreciated.

Martelie




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Vlookup with validation Brian Excel Worksheet Functions 4 May 4th 23 03:43 AM
Master invoice number Christopher M. Excel Worksheet Functions 0 July 24th 05 04:26 PM
Question on Vlookup dharmik Excel Worksheet Functions 4 July 22nd 05 02:12 AM
How to format a number in Indian style in Excel? Victor_alb Excel Discussion (Misc queries) 2 December 21st 04 05:21 AM
Defining a number in a cell by text then subtracting it by the tex Crowraine Excel Worksheet Functions 1 December 16th 04 08:49 AM


All times are GMT +1. The time now is 05:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"