Ordinal Position Function
A function which returns ordinal position of one specific element in an array as a number. Alternative name occurrence.
So for example if the array was like this
A1 Dave
A2 Fred
A3 Jim
A4 Dave
Then Ordinal(A1:A4,A1) would return 1 eg the 1st Occurence of Dave
Ordinal(A1:A4,A2) would return 1 eg the 1st Occurrence of Fred
Ordinal(A1:A4,A3) would return 1 eg the 1st Occurence of Jim
Ordinal(A1:A4,A4) would return 2 eg the 2nd Occurrence of Dave
Function Format something like
Ordinal(lookup array, lookup cell, optional reverse)
Lookup array could be vertical with a width of 1 column or horizontal with a height of 1 row.
Lookup cell must be in the lookup array
Reverse would count from the bottom or right rather than top or left.
Attached picture is a more concrete example.
Should work in Tables too.
The only work around I have for this is a real cludge and relies on the list being sorted. Personally I have needed something like this a few times.
It could also be useful for check that data entered doesn't contain duplicates, although can also use countif function for that.
To syntactic sugar extensions would be FirstOrdinal and LastOrdinal which return true if the element is the first or last respectively, but this would be possible with the Ordinal function and Countif function within an If function
A slight longer example in the screen shot (done by hand)
4 comments

Sergei Baklan commented
Hi Steve,
With structured references it also works like
=COUNTIF(OFFSET(tblOne[[#Headers],[Opponent]],1,0):[@Opponent],[@Opponent]) 
Steve A commented
Sergei. Thanks your solution which works well, and certainly simplifies my method. I am kicking myself didn't figure it out, obviously over thought it.
Having said that, a proper function would be still be good, because when used in tables you could use structured references.
Roy: Thanks for your time, didn't quite follow it, but assuming you are correct a lot of shenanigans to get it to work. 
Sergei Baklan commented
In general COUNTIF($A$1:A1,A1) is Ordinla(A1:A4,A1), etc.... COUNTIF($A$1:A4,A4)

Roy commented
It'd be nice.
There is a fairly standard workaround for that if you need the functionality. Basically, you MATCH() the test value against the range the results of which Excel sees as an array of TRUE and FALSE. With the kind of thing you are looking to do, there would presumably be only one match, so only one TRUE in the array Excel sees internally.
Excel would be happy to see that as an array of 1's and 0's, but won't on its own, so you multiply that result by a generic array. It could be an array of just 1's, but that would not solve your need. A way to produce an array of sequential numbers is to use the ROW() function in an array formula (the "CSE" kind, not the new ones) like so:
ROW(1:4)
(you can hard code the 1 and 4, or do it with functions). That will produce, internally in the formula, "{1;2;3;4}" and if you multiply the first part by this part, Excel will go elementwise through both lists and give you, say
{0;1;0;0} * {1;2;3;4} = {0;2;0;0}
(I said "you" but really, still Excel internally).
So far, so good. Now SUM() that result and you get "2" for a formula output. That happens to be the test value's ordinal position in the range.
Depending upon one's needs, one can modify the approach. If you ever see a formula that has " " at the start (usually), or "+ +", it is assuredly doing this kind of work. The reason is you have to perform an operation on the internal result to make it a single value for output. The above description used SUM() and was really more of this calculation: {1} * {1;2;3;4} than the simple version I mentioned above.
Why the shenanigans though, right? It should be straightforward. That there has been a taught workaround for so long CLEARLY shows the huge need for a direct, natural way to do this!