|
Excel 2002 : How to extract references from a list ?
Dear Sir,
I have the following table in a worksheet : LIST 1 A B C 1 Staff No Staff Name Account No. 2 2120 John 21645872 3 2121 Sally 21645712 4 2146 Paul 21457126 5 2184 Jenny 21687430 20 3358 Kenny 21477410 Below this list , I have another table beginning from row 26 to be completed : LIST 2 A B C D Account No Staff No Date Amount 26 2184 26/9/06 236.34 27 2121 27/9/06 300.50 28 2121 28/9/06 250.34 29 2146 28/9/06 450.00 30 3358 26/9/06 500.00 250 2120 31/12/06 230.00 I need to fill up the Account Number at A26 to A250 for each staff in List 2 by referring to the staff number in List 1. May I know what formula I must input at Cell A26 and copy it down to A 250 to get the straight answer ? Thank You Low Seng Kuang -- A36B58K641 |
Excel 2002 : How to extract references from a list ?
This should work: =VLOOKUP(B26,$A$2:$C$20,3,FALSE)
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Mr. Low" wrote in message ... Dear Sir, I have the following table in a worksheet : LIST 1 A B C 1 Staff No Staff Name Account No. 2 2120 John 21645872 3 2121 Sally 21645712 4 2146 Paul 21457126 5 2184 Jenny 21687430 20 3358 Kenny 21477410 Below this list , I have another table beginning from row 26 to be completed : LIST 2 A B C D Account No Staff No Date Amount 26 2184 26/9/06 236.34 27 2121 27/9/06 300.50 28 2121 28/9/06 250.34 29 2146 28/9/06 450.00 30 3358 26/9/06 500.00 250 2120 31/12/06 230.00 I need to fill up the Account Number at A26 to A250 for each staff in List 2 by referring to the staff number in List 1. May I know what formula I must input at Cell A26 and copy it down to A 250 to get the straight answer ? Thank You Low Seng Kuang -- A36B58K641 |
Excel 2002 : How to extract references from a list ?
=VLOOKUP(B26,A$1:C$20,3) and copy down
-- Gary's Student "Mr. Low" wrote: Dear Sir, I have the following table in a worksheet : LIST 1 A B C 1 Staff No Staff Name Account No. 2 2120 John 21645872 3 2121 Sally 21645712 4 2146 Paul 21457126 5 2184 Jenny 21687430 20 3358 Kenny 21477410 Below this list , I have another table beginning from row 26 to be completed : LIST 2 A B C D Account No Staff No Date Amount 26 2184 26/9/06 236.34 27 2121 27/9/06 300.50 28 2121 28/9/06 250.34 29 2146 28/9/06 450.00 30 3358 26/9/06 500.00 250 2120 31/12/06 230.00 I need to fill up the Account Number at A26 to A250 for each staff in List 2 by referring to the staff number in List 1. May I know what formula I must input at Cell A26 and copy it down to A 250 to get the straight answer ? Thank You Low Seng Kuang -- A36B58K641 |
Excel 2002 : How to extract references from a list ?
or....
=INDEX($C$2:$C$20,MATCH(B26,$A$2:$A$20,0)) "Mr. Low" wrote: Dear Sir, I have the following table in a worksheet : LIST 1 A B C 1 Staff No Staff Name Account No. 2 2120 John 21645872 3 2121 Sally 21645712 4 2146 Paul 21457126 5 2184 Jenny 21687430 20 3358 Kenny 21477410 Below this list , I have another table beginning from row 26 to be completed : LIST 2 A B C D Account No Staff No Date Amount 26 2184 26/9/06 236.34 27 2121 27/9/06 300.50 28 2121 28/9/06 250.34 29 2146 28/9/06 450.00 30 3358 26/9/06 500.00 250 2120 31/12/06 230.00 I need to fill up the Account Number at A26 to A250 for each staff in List 2 by referring to the staff number in List 1. May I know what formula I must input at Cell A26 and copy it down to A 250 to get the straight answer ? Thank You Low Seng Kuang -- A36B58K641 |
Excel 2002 : How to extract references from a list ?
Hello Bernard,
Yes, your suggested formula works vewry well. By the way may I know the what is the fourth augument FALSE stand for ? Many thanks Low -- A36B58K641 "Bernard Liengme" wrote: This should work: =VLOOKUP(B26,$A$2:$C$20,3,FALSE) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Mr. Low" wrote in message ... Dear Sir, I have the following table in a worksheet : LIST 1 A B C 1 Staff No Staff Name Account No. 2 2120 John 21645872 3 2121 Sally 21645712 4 2146 Paul 21457126 5 2184 Jenny 21687430 20 3358 Kenny 21477410 Below this list , I have another table beginning from row 26 to be completed : LIST 2 A B C D Account No Staff No Date Amount 26 2184 26/9/06 236.34 27 2121 27/9/06 300.50 28 2121 28/9/06 250.34 29 2146 28/9/06 450.00 30 3358 26/9/06 500.00 250 2120 31/12/06 230.00 I need to fill up the Account Number at A26 to A250 for each staff in List 2 by referring to the staff number in List 1. May I know what formula I must input at Cell A26 and copy it down to A 250 to get the straight answer ? Thank You Low Seng Kuang -- A36B58K641 |
Excel 2002 : How to extract references from a list ?
Hello Gary,
Many thanks, the formula you suggested works out well. Kind Regards Low Seng Kuang A36B58K641 "Gary''s Student" wrote: =VLOOKUP(B26,A$1:C$20,3) and copy down -- Gary's Student "Mr. Low" wrote: Dear Sir, I have the following table in a worksheet : LIST 1 A B C 1 Staff No Staff Name Account No. 2 2120 John 21645872 3 2121 Sally 21645712 4 2146 Paul 21457126 5 2184 Jenny 21687430 20 3358 Kenny 21477410 Below this list , I have another table beginning from row 26 to be completed : LIST 2 A B C D Account No Staff No Date Amount 26 2184 26/9/06 236.34 27 2121 27/9/06 300.50 28 2121 28/9/06 250.34 29 2146 28/9/06 450.00 30 3358 26/9/06 500.00 250 2120 31/12/06 230.00 I need to fill up the Account Number at A26 to A250 for each staff in List 2 by referring to the staff number in List 1. May I know what formula I must input at Cell A26 and copy it down to A 250 to get the straight answer ? Thank You Low Seng Kuang -- A36B58K641 |
Excel 2002 : How to extract references from a list ?
Hello Madam,
The formula you suggested works out well. Many thanks Low -- A36B58K641 "Teethless mama" wrote: or.... =INDEX($C$2:$C$20,MATCH(B26,$A$2:$A$20,0)) "Mr. Low" wrote: Dear Sir, I have the following table in a worksheet : LIST 1 A B C 1 Staff No Staff Name Account No. 2 2120 John 21645872 3 2121 Sally 21645712 4 2146 Paul 21457126 5 2184 Jenny 21687430 20 3358 Kenny 21477410 Below this list , I have another table beginning from row 26 to be completed : LIST 2 A B C D Account No Staff No Date Amount 26 2184 26/9/06 236.34 27 2121 27/9/06 300.50 28 2121 28/9/06 250.34 29 2146 28/9/06 450.00 30 3358 26/9/06 500.00 250 2120 31/12/06 230.00 I need to fill up the Account Number at A26 to A250 for each staff in List 2 by referring to the staff number in List 1. May I know what formula I must input at Cell A26 and copy it down to A 250 to get the straight answer ? Thank You Low Seng Kuang -- A36B58K641 |
| All times are GMT +1. The time now is 06:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com