member lookup

  • Thread starter Thread starter Martint2k4
  • Start date Start date
M

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'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

Frank said:
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
Click to expand...
 
Alan Beban said:
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)
Click to expand...

Hi Alan
nice trick :-)
Cheers
Frank
 
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)
Click to expand...
...

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.
 
As Harlan Grove is wont to say: "Testing's a bitch."

Substitute MATCH($A1 for MATCH(A1

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

Substitute MATCH($A1 for MATCH(A1
Click to expand...
...

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.
 
Back
Top