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

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

This is the same question that you asked and I answered yesterday, albeit
with two less conditions. What is wrong with the previous answer?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cjjoo" wrote in
message ...

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

in your previous solution, if x123 comes into the workshop twice or
thrice a day,(date_in and date_out)

the id will not be unique.

this is why i try to simply the problem to get something out of it.

e.g:

id vehicle_no date_in date_out
1 x123 01/10/2005
1 x123 01/10/2005 01/10/2005
2 x123 01/10/2005
2 x123 01/10/2005 01/10/2005
3 x123 01/10/2005

the key thing is that a new ID will be issued to x123 once i has been
dated_out.

all these have to be key in on the same day and there are more than
five

vehicles and they have to fulfill the same criteria.


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