If the columns must be ordered as you've shown, then match and index can do
the trick. In A4: =INDEX(A$1:A3,MATCH(B4,B$1:B3,FALSE)). This returns #N/A
unless you enter a name in B4 that appears above that cell. The formula can
also be filled through column A.
If you can reverse the columns, vlookup is simpler. But it requires that
the key field be in the left-most column of your table.
--Bruce

"cjjoo" wrote:


this is my problem:


A B
1 ann
2 bob
3 catie
1 ann


in clm B, the names are in a drop-down list . So in the last row, once
ann is

assigned, it is possible in A4 that it returns the number that has been
previously

assigned to ann? so if i key in catie in B5, A5 should return 3.


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=477774