Formula to locate next occurance of data

[tkroper]

Hi friends!

This formula will locate specified text in column A and return the data from the same row in column B:

=VLOOKUP("Claimant Name:",$A$1:$B$22,2,0)

I would like to locate the next occurance of the same text and return the data in the corresponding row until I get each occurance (there are nearly 1,500 rows). The lookup value is not always the same number of rows below the prior occurance.

 

[tkroper]

I may have found what I'm looking for:

=INDEX($A$1:$B$1500,SMALL(IF($A$1:$A$1500=$A$8,ROW($A$1:$A$1500)),ROW(1:1)),2)

enter the formula as an array formula (CTRL+SHIFT+ENTER)

I found it here: http://office.microsoft.com/en-us/e...ultiple-corresponding-values-HA001226038.aspx