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.

[bpeltzer]

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
>
>

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[cjjoo]

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.