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

"Harlan Grove" wrote:

"T. Valko" wrote...
....
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(Da ta)),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.