vlookup for client list

[xololady]

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

 

[Stefi]

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:

 

[xololady]

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.

 

[L. Howard Kittle]

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

 

[Jacob Skaria]

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]

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

 

[Stefi]

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;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;I2)
returns sex, etc.

Regards,
Stefi

„xololady†ezt írta:

 

[Francis]

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