Find thee last value in a row with duplicates

Hi,

I have a sheet with numbers in descending orders with duplicate in one row and their respective value in 22nd row. I want to find the2nd occurrence of that value. (max. occurrence2 is 2)

Foe example

5----4-----3-----3-----2-----2-----1
A----B-----0---- C-----0-----D-----E

So if i lockup value "3" C should be returned (not 0) and for 2 "D"

see attached also

Thanks a lot

With your selection in Cell E1 try

=IF(COUNTIF($C$3:$I$3,$E$1)>1,INDEX($C$4:$I$4,SMALL(INDEX(($C$3:$I$3=$E$1)*(COLUMN($C$3:$I$3)-ROW($C$3)+1),),COUNTIF($C$3:$I$3,"<>"&$E$1)+2)),INDEX($C$4:$I$4,MATCH(E1,$C$3:$I$3,0)))

Using your posted workbook.
A1: a value to find in Row_3...3

This regular formula returns the corresponding Row_4 item for the last match:

B1: =INDEX(4:4,MAX(INDEX((C3:I3=A1)*COLUMN(C3:I3),0)))

In that example, the formula returns: c

Is that something you can work with?

In other words, you want to match the last (right-most) instance of the lookup value?

Try this...

Data Range

	A	B	C	D	E	F	G
1	5	4	3	3	2	2	1
2	A	B	0	C	0	D	E
3	-------	-------	-------	-------	-------	-------	-------
4	Lookup	Result
5	3	C

This formula entered in B5:

=LOOKUP(2,1/(A1:G1=A5),A2:G2)

All worked
Thanks

You're welcome. We appreciate the feedback!