Use match formula and exclude match from next search

[nando88]

I want to use a match formula to look for a value in a range of cells, but if there are repeated values, I would like to exclude the first match from the search, that way I don't use the value of the first match again.
I want to do this with a formula and without vba, if possible.
Thanks.

 

[lrobbo314]

This is kind of ugly, but it seems to work. I have the lookup value in O6 and the lookup range is in R6:R11. And the formula to give the relative position of the lookup value in the range is...

=IF(COUNTIF($R$6:$R$11,O6)>1,MATCH(O6,INDIRECT("R" & MATCH(O6,$R$6:$R$11,0) + ROW(R6) & ":R" & ROW(R11)),0)+MATCH(O6,$R$6:$R$11,0),MATCH(O6,$R$6:$R$11,0))

The countif sees if there is more than 1 of the lookup value in the lookup range. If yes, then it uses match with indirect, otherwise it does a regular match.

 

[nando88]

This is kind of ugly, but it seems to work. I have the lookup value in O6 and the lookup range is in R6:R11. And the formula to give the relative position of the lookup value in the range is...

=IF(COUNTIF($R$6:$R$11,O6)>1,MATCH(O6,INDIRECT("R" & MATCH(O6,$R$6:$R$11,0) + ROW(R6) & ":R" & ROW(R11)),0)+MATCH(O6,$R$6:$R$11,0),MATCH(O6,$R$6:$R$11,0))

The countif sees if there is more than 1 of the lookup value in the lookup range. If yes, then it uses match with indirect, otherwise it does a regular match.

I just tried your answer, but it partially works.
It ignores the first value, but I need to find the first value and then ignore it and find the second.
Is this possible?
Thanks