Find the next occurance

[Lou]

I want to find each occurance of a certain value in a worksheet. The value
can occur once or several times. Each time the value occurs, I need to list
related data.

Hlookup works great to find the first occurance, but how do I get it to find
the next occurance and then the next, etc?

 

[T. Valko]

Hlookup works great to find the first occurance, but how do

I get it to find the next occurance and then the next, etc?

You have to use a different formula.

Where are the lookup_values? Are they sorted so that they might be grouped
together? Where is the related data that you want returned?

 

[Lou]

The look up values are in row 1. The related data that I need is in rows 3, 4
and 5. However, there is related data in up to 45 rows. I am trying to get
the look up values and related data listed in a seperate worksheet as a
summary.
Thanks

 

[T. Valko]

However, there is related data in up to 45 rows.

Ugh! A formula method probably isn't the best way to go. Maybe a pivot table
?????

 

[Lou]

I'll give that a try. Thanks.

Ugh! A formula method probably isn't the best way to go. Maybe a pivot table
?????

 

[Harlan Grove]

Ugh! A formula method probably isn't the best way to go. Maybe a pivot table
?????

....

Or maybe not. If the OP is fetching data with HLOOKUP, that would
imply identifying values are in row 1 rather than field names. OP
would need to transpose the data and add field names to use a pivot
table. Then there's the question whether any of the data the OP needs
to pull would be text. Pivot tables can't do much with text in the
Data area of a pivot table.

If the data were in a range named Data with possibly duplicate
identifiers in row 1 and fields in different rows rather than
different columns, one way of fetching all data for identifier X would
involve formulas like

A1:
=MATCH(X,INDEX(Data,1,0),0)

A2:
=INDEX(Data,3,A1)

A3:
=INDEX(Data,4,A1)

A4:
=INDEX(Data,5,A1)

which will return the column index of the first match in A1 and the
data in rows 3, 4 and 5 in that column in A2, A3 and A4, respectively.
Then fetch the next possible match in column B using

B1:
=IF(COUNTIF(INDEX(Data,1,0),X)>COLUMNS($A1:A1),
MATCH(X,INDEX(Data,1,A1+1):INDEX(Data,1,COLUMNS(Data)),0)+A1,"")

B2:
=IF(B$1<>"",INDEX(Data,3,B1),"")

B3:
=IF(B$1<>"",INDEX(Data,4,B1),"")

B4:
=IF(B$1<>"",INDEX(Data,5,B1),"")

Then copy B1:B4 and fill right as far as needed.

 

[Lou]

Thanks Harlan, it works great! Sorry for the delayed reponse, it took me a
while to to get my head around what you suggested.