I am working on a billing spreadsheet. Each client has a client ID plus
other details over 11 columns. I created client list with their specific
info. When I try to use a VLOOKUP formula on new worksheet, I can only get
client ID and name (first 2 columns) without the other columns. I want to be
able to type client id number and have the rest of the info automatically
fill in the row. Please tell me what I am missing.

Thank you, Karen

Maybe you didn't specify the right tabla array or Col_index_num. E.g. if you
have Id + 2 columns of data in columns A:C of Sheet2 then
=VLOOKUP(A2;Sheet2!A:C;2;FALSE)
returns value from column B,
=VLOOKUP(A2;Sheet2!A:C;3;FALSE)
returns value from column C.
Regards,
Stefi

€žxololady€� ezt Ã*rta:

I am working on a billing spreadsheet. Each client has a client ID plus
other details over 11 columns. I created client list with their specific
info. When I try to use a VLOOKUP formula on new worksheet, I can only get
client ID and name (first 2 columns) without the other columns. I want to be
able to type client id number and have the rest of the info automatically
fill in the row. Please tell me what I am missing.

Thank you, Karen

Is it possible to fill in several columns at the same time with different
info, like name, birthdate, sex, etc? If not Vlookup, is there another
formula? I think Vlookup is only useful for one column of info.

"Stefi" wrote:

Maybe you didn't specify the right tabla array or Col_index_num. E.g. if you
have Id + 2 columns of data in columns A:C of Sheet2 then
=VLOOKUP(A2;Sheet2!A:C;2;FALSE)
returns value from column B,
=VLOOKUP(A2;Sheet2!A:C;3;FALSE)
returns value from column C.
Regards,
Stefi

€žxololady€� ezt Ã*rta:

I am working on a billing spreadsheet. Each client has a client ID plus
other details over 11 columns. I created client list with their specific
info. When I try to use a VLOOKUP formula on new worksheet, I can only get
client ID and name (first 2 columns) without the other columns. I want to be
able to type client id number and have the rest of the info automatically
fill in the row. Please tell me what I am missing.

Thank you, Karen

Hi Karen,

This should work for you.

=VLOOKUP(A8,B1:L5,{2,3,4,5,6,7,8,9,10,11},0)

Where A8 has the client ID to be looked up, (perhaps using a data valadation
drop down).

B1:L5 is the client information, with the client ID's in the B column and
the associated info for that client in the 10 cells to the right.

Select 10 cells in the row where you want the client data to be displayed,
something like C8:L8. While still selected, type the formula above in the
'white' cell of the selected cells. Now hold Crtl Shift down and hit
Enter.

You will get curly brackets around the formula in the formula bar and your
data should be displayed in the selected cells. (Do not type the curly
brackets into the formula, Excel will do that when you commit the formula.

If you need to edit/change the formula you must select ALL the cells again,
make your changes and use CTRL SHIFT ENTER again.

HTH
Regards,
Howard

"xololady" wrote in message
...
I am working on a billing spreadsheet. Each client has a client ID plus
other details over 11 columns. I created client list with their specific
info. When I try to use a VLOOKUP formula on new worksheet, I can only
get
client ID and name (first 2 columns) without the other columns. I want to
be
able to type client id number and have the rest of the info automatically
fill in the row. Please tell me what I am missing.

Thank you, Karen

Just relook at VLOOKUP function help..

=VLOOKUP(lookupvalue,A:K,3,0)
should return the value from column C (which is the 3rd column of array A:K)

=VLOOKUP(lookupvalue,B:K,2,0)
should return the value from column C (which is the 2nd column of array B:E)

If this post helps click Yes
---------------
Jacob Skaria


"xololady" wrote:

I am working on a billing spreadsheet. Each client has a client ID plus
other details over 11 columns. I created client list with their specific
info. When I try to use a VLOOKUP formula on new worksheet, I can only get
client ID and name (first 2 columns) without the other columns. I want to be
able to type client id number and have the rest of the info automatically
fill in the row. Please tell me what I am missing.

Thank you, Karen

You can try this formula in the left top cell and copy down/across as
required..
=VLOOKUP(lookupvalue,B:K,COLUMN(A2),0)

If this post helps click Yes
---------------
Jacob Skaria


"xololady" wrote:

Is it possible to fill in several columns at the same time with different
info, like name, birthdate, sex, etc? If not Vlookup, is there another
formula? I think Vlookup is only useful for one column of info.

"Stefi" wrote:

Maybe you didn't specify the right tabla array or Col_index_num. E.g. if you
have Id + 2 columns of data in columns A:C of Sheet2 then
=VLOOKUP(A2;Sheet2!A:C;2;FALSE)
returns value from column B,
=VLOOKUP(A2;Sheet2!A:C;3;FALSE)
returns value from column C.
Regards,
Stefi

€žxololady€� ezt Ã*rta:

I am working on a billing spreadsheet. Each client has a client ID plus
other details over 11 columns. I created client list with their specific
info. When I try to use a VLOOKUP formula on new worksheet, I can only get
client ID and name (first 2 columns) without the other columns. I want to be
able to type client id number and have the rest of the info automatically
fill in the row. Please tell me what I am missing.

Thank you, Karen

VLOOKUP can do what you are looking for, in this way:
If name is in column B then
=VLOOKUP(A2;Sheet2!A:B;2;FALSE)
returns name,
if birthdate is in column C then
=VLOOKUP(A2;Sheet2!A:C;3;FALSE)
returns birthdate,
if sex is in column D then
=VLOOKUP(A2;Sheet2!A:D;4;FALSE)
returns sex, etc.

Another way is MATCH and INDEX, in this case you have to use a helper column
(in my example I) with
=MATCH(A2;Sheet2!A:A;0)
in it, and
=INDEX(Sheet2!B:B;I2)
returns name,
=INDEX(Sheet2!C:C;I2)
returns birthdate,
=INDEX(Sheet2!D:D;I2)
returns sex, etc.

Regards,
Stefi

€žxololady€� ezt Ã*rta:

Is it possible to fill in several columns at the same time with different
info, like name, birthdate, sex, etc? If not Vlookup, is there another
formula? I think Vlookup is only useful for one column of info.

"Stefi" wrote:

Maybe you didn't specify the right tabla array or Col_index_num. E.g. if you
have Id + 2 columns of data in columns A:C of Sheet2 then
=VLOOKUP(A2;Sheet2!A:C;2;FALSE)
returns value from column B,
=VLOOKUP(A2;Sheet2!A:C;3;FALSE)
returns value from column C.
Regards,
Stefi

€žxololady€� ezt Ã*rta:

I am working on a billing spreadsheet. Each client has a client ID plus
other details over 11 columns. I created client list with their specific
info. When I try to use a VLOOKUP formula on new worksheet, I can only get
client ID and name (first 2 columns) without the other columns. I want to be
able to type client id number and have the rest of the info automatically
fill in the row. Please tell me what I am missing.

Thank you, Karen

It is possible to do what you want,
" Is it possible to fill in several columns at the same time with different
info, like name, birthdate, sex, etc? "

Mutiple Vlookups will do the jobs.
Just change the column reference, from

=VLOOKUP(A2;Sheet2!A:C;2;FALSE) to
=VLOOKUP(A2;Sheet2!A:C;3;FALSE)

Post back id you need more clarifications


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis




"xololady" wrote:

Is it possible to fill in several columns at the same time with different
info, like name, birthdate, sex, etc? If not Vlookup, is there another
formula? I think Vlookup is only useful for one column of info.

"Stefi" wrote:

Maybe you didn't specify the right tabla array or Col_index_num. E.g. if you
have Id + 2 columns of data in columns A:C of Sheet2 then
=VLOOKUP(A2;Sheet2!A:C;2;FALSE)
returns value from column B,
=VLOOKUP(A2;Sheet2!A:C;3;FALSE)
returns value from column C.
Regards,
Stefi

€žxololady€� ezt Ã*rta:

I am working on a billing spreadsheet. Each client has a client ID plus
other details over 11 columns. I created client list with their specific
info. When I try to use a VLOOKUP formula on new worksheet, I can only get
client ID and name (first 2 columns) without the other columns. I want to be
able to type client id number and have the rest of the info automatically
fill in the row. Please tell me what I am missing.

Thank you, Karen