match index?

[cjjoo]

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.

 

[Guest]

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

 

[Bob Phillips]

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]

in your previous solution, if x123 comes into the workshop twice o
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 bee
dated_out.

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

vehicles and they have to fulfill the same criteria