Excel 2002 : How to extract references from a list ?

[Guest]

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

 

[Bernard Liengme]

This should work: =VLOOKUP(B26,$A$2:$C$20,3,FALSE)
best wishes

 

[Guest]

=VLOOKUP(B26,A$1:C$20,3) and copy down

 

[Guest]

or....

=INDEX($C$2:$C$20,MATCH(B26,$A$2:$A$20,0))

 

[Guest]

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

 

[Guest]

Hello Gary,

Many thanks, the formula you suggested works out well.


Kind Regards

Low Seng Kuang


A36B58K641

 

[Guest]

Hello Madam,

The formula you suggested works out well.

Many thanks


Low