vlookup with more than number to be retrieved
 

Roger, Thank you first of all for replying..
I could do the first bit.. "=B1&" | "&C1 "
but then with the vlookup formula..
do i type "=vlookup(a1,what table?, what is offset?, is 0 false?)

Any chance you could make it more clear. Im sure its crystal clear to
everyone else.. but i am dumb.

Martelie



Roger Govier Wrote:
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



-


--
martelie

Hi Martelie

Supposing your original table was A1:C100.
If you have now inserted a new column C, so the table range would be
A1:D100 but the area that you would be concerned with would be C1:D100
as you would not need the A and B columns as they had been combined into
the new column C.

So, =VLOOKUP("John | 56789"Sheet1!C1:D100,2,0)

Change the sheet reference and range to suit.
The offset in the above is 2, which is telling Vlookup to take it from
the 2nd column of the table.
The 0 at the end is exactly the same as typing FALSE for the 4th parameter.

I hope this helps, but don't hesitate to post back if you need more
explanation. You are not "dumb" at all. Everyone has to go through the
learning curve, and we never do stop learning. If you want you can post
a copy of your sheet directly to me, (not to the Newsgroup please) by
removing the "nospam" from my email address.

Regards

Roger Govier



martelie wrote:

Roger, Thank you first of all for replying..
I could do the first bit.. "=B1&" | "&C1 "
but then with the vlookup formula..
do i type "=vlookup(a1,what table?, what is offset?, is 0 false?)

Any chance you could make it more clear. Im sure its crystal clear to
everyone else.. but i am dumb.

Martelie



Roger Govier Wrote:


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



-

Roger,
Thank you for all the help.

You know the first bit... "=B1&" | "&C1 " .. is it possible to combine
3 columns?

Martelie



Roger Govier Wrote:
Hi Martelie

Supposing your original table was A1:C100.
If you have now inserted a new column C, so the table range would be
A1:D100 but the area that you would be concerned with would be C1:D100
as you would not need the A and B columns as they had been combined
into
the new column C.

So, =VLOOKUP("John | 56789"Sheet1!C1:D100,2,0)

Change the sheet reference and range to suit.
The offset in the above is 2, which is telling Vlookup to take it from
the 2nd column of the table.
The 0 at the end is exactly the same as typing FALSE for the 4th
parameter.

I hope this helps, but don't hesitate to post back if you need more
explanation. You are not "dumb" at all. Everyone has to go through the
learning curve, and we never do stop learning. If you want you can
post
a copy of your sheet directly to me, (not to the Newsgroup please) by
removing the "nospam" from my email address.

Regards

Roger Govier


--
martelie

Hi Martelie

Yes, combine as many as you wish, subject to the limit of a formula not
exceeding 1024 characters.

Regards

Roger Govier



martelie wrote:

Roger,
Thank you for all the help.

You know the first bit... "=B1&" | "&C1 " .. is it possible to combine
3 columns?

Martelie



Roger Govier Wrote:


Hi Martelie

Supposing your original table was A1:C100.
If you have now inserted a new column C, so the table range would be
A1:D100 but the area that you would be concerned with would be C1:D100
as you would not need the A and B columns as they had been combined
into
the new column C.

So, =VLOOKUP("John | 56789"Sheet1!C1:D100,2,0)

Change the sheet reference and range to suit.
The offset in the above is 2, which is telling Vlookup to take it from
the 2nd column of the table.
The 0 at the end is exactly the same as typing FALSE for the 4th
parameter.

I hope this helps, but don't hesitate to post back if you need more
explanation. You are not "dumb" at all. Everyone has to go through the
learning curve, and we never do stop learning. If you want you can
post
a copy of your sheet directly to me, (not to the Newsgroup please) by
removing the "nospam" from my email address.

Regards

Roger Govier