| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Want to Create a List in Excel 2002; Don't see List in Data Menu? | Excel Discussion (Misc queries) | |||
| how to use list in excel 2002 | Excel Worksheet Functions | |||
| Recently Used File List - 2002 Contains 'Temp' Files | Excel Discussion (Misc queries) | |||
| hyperlinks not working in excel 2002 SP3 | Excel Discussion (Misc queries) | |||
| External data references in EXCEL 2002 | Excel Discussion (Misc queries) | |||
Linear Mode
