ExcelBanter - =OFFSET(MATCH(My frustration continues!))

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - =OFFSET(MATCH(My frustration continues!)) (https://www.excelbanter.com/excel-programming/414218-%3Doffset-match-my-frustration-continues.html)

confused

=OFFSET(MATCH(My frustration continues!))

I have text/data in cells (A1:B10)
B1 and B10 have the same data, with different corresponding entries in
Column A.

In C1 I have the formula
=OFFSET(A1:B6,MATCH(Sheet1!B1,OFFSET(A1:B6,0,1,ROW S(A1:B6),1),0)-1,0,1,1)
which gives me the data in A1, but....

what I want is to be able to look up the 2nd occurence (B10) and get the
corresponding data in A10.

Mike H

=OFFSET(MATCH(My frustration continues!))

Try

=LOOKUP(2,1/(B1:B10=somevalue),A1:A10)

Mike

"Confused" wrote:

I have text/data in cells (A1:B10)
B1 and B10 have the same data, with different corresponding entries in
Column A.

In C1 I have the formula
=OFFSET(A1:B6,MATCH(Sheet1!B1,OFFSET(A1:B6,0,1,ROW S(A1:B6),1),0)-1,0,1,1)
which gives me the data in A1, but....

what I want is to be able to look up the 2nd occurence (B10) and get the
corresponding data in A10.

All times are GMT +1. The time now is 06:12 AM.