Offset 2nd ref.

[houAstros1989]

A B
ONE 1
TWO 2
THREE 3
ONE 4


Is there a way I can use the offset to look up a duplicate reference? To
where I ref ONE and come up with 4.

 

[Jacob Skaria]

Try the below array formula which will lookup and return the 2nd matching value
=INDEX(B1:B10,SMALL(IF(A1:A10="One",ROW(A1:A10)),2))

With text 'One" in cell C1 and the instance number in cell D1
(array entered using Ctrl+Shift+Enter instead of Enter)
=INDEX(B1:B10,SMALL(IF(A1:A10=C1,ROW(A1:A10)),D1))

will return the last matching value in B if data is continuous (just works
for your sample).
=LOOKUP("one",A:A,B:B)
=VLOOKUP("one",A:B,2,1)

 

[Ashish Mathur]

Hi,

This array formula (Ctrl+Shift+Enter) will return the value against the last
occurrence of One. B10 contains One. I have assumed that the data is in
range B4:C7

=INDEX($B$4:$C$7,1*MAX(($B$4:$B$7=B10)*(ROW($B$4:$B$7)-ROW($B$3))),2)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Hi,

You may also try this non array formula

=LOOKUP(2,1/($B$4:$B$7=B10),C4:C7)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com