member lookup

[Martint2k4]

Hi, i have created a spreadt sheet with 20 members and thei
information, what i want to know is, how do i make another worksheet
with a formula, so when i type in a members id number, all th
information shows up ,

thank

 

[Frank Kabel]

Hi
you may use VLOOKUP for this.
e.g. if you enter the member ID in cell A1 try the following in B1
=VLOOKUP(A1,'sheet1'!$A$1:$E$20,2,0)

have a look at
http://www.mvps.org/dmcritchie/excel/vlookup.htm
for more information about VLOOKUP

 

[Alan Beban]

Frank Kabel's suggestion will return only the data from Column 2 of the
table; to get all member data, array enter the following in B1:E1:

=VLOOKUP(A1,'sheet1'!$A$1:$E$20,{2,3,4,5},0)

Alan Beban

Hi
you may use VLOOKUP for this.
e.g. if you enter the member ID in cell A1 try the following in B1
=VLOOKUP(A1,'sheet1'!$A$1:$E$20,2,0)

have a look at
http://www.mvps.org/dmcritchie/excel/vlookup.htm
for more information about VLOOKUP

 

[Frank Kabel]

Frank Kabel's suggestion will return only the data from Column 2 of the
table; to get all member data, array enter the following in B1:E1:

=VLOOKUP(A1,'sheet1'!$A$1:$E$20,{2,3,4,5},0)

Hi Alan
nice trick
Cheers
Frank

 

[Harlan Grove]

Frank Kabel's suggestion will return only the data from Column 2 of the
table; to get all member data, array enter the following in B1:E1:

=VLOOKUP(A1,'sheet1'!$A$1:$E$20,{2,3,4,5},0)

...

That requires array entry and a hard-coded array as 3rd argument. Implicit range
indexing could be used if each result field were in the same column as in the
original table. If so, then the *nonarray* formula

=INDEX('sheet1'!$A$1:$E$20,MATCH(A1,'sheet1'!$A$1:$A$20,0),0)

could be entered in B1 to pull the col B result from 'sheet1'!$A$1:$E$20, then
B1 could be filled right into C1:E1 to pull the corresponding cols C, D and E
values from 'sheet1'!$A$1:$E$20. Just an alternative.

 

[Alan Beban]

As Harlan Grove is wont to say: "Testing's a bitch."

Substitute MATCH($A1 for MATCH(A1

Alan Beban

 

[Harlan Grove]

As Harlan Grove is wont to say: "Testing's a bitch."

Substitute MATCH($A1 for MATCH(A1

...

Testing is nice, but this was pure carelessness - I tested in a row other than
1, but just copied and pasted the A1 from your formula, overwriting the $. Still
a dumb mistake by me, but diagnosis incorrect.