Lookup from datasets with a single identifier and varying row count

I'm a little bit experienced using the various LOOKUP functions, and can even plod through some INDEX() MATCH() combinations...but I can't figure out a way to pull data from a sheet that has multiple data sets of varying row counts.

Each data set has a single identifier, then a varying number of rows, with a constant number of columns. Each dataset is listed from top to bottom of the sheet. I'd like to pull specific columns across to another sheet, and arrange these copied data sets horizontally across the sheet.

Ideally, I'd like to keep this formula-based, because macros have had a history of being forbidden in the regulated environment I'm in. (It's possible that a macro could be approved, but it's doubtful.)

I've attached an example of what I'm looking for.

Many thanks in advance to anyone who can answer. Many more thansk to anyone who can answer AND explain it to me like I'm a five year old.

I think the only solution is VBA because the variability of the data does not lend itself to formulae (which generally prefer regular patterns!)

For example, finding the text "Sample Name" (to get the sample code) is a problem as Excel "lookups" find the first occurrence of a "match".

I appreciate a VBA solution is not your preferred option but here is one anyway. I will see if I can come up with formula-based solution.

RUN button on first sheet initiates the macro.

Please Login or Register  to view this content.

I have added a formula-based solution in sheet "Formulas".

The cells in A1:C5 are used to determine the row number of the "Sample Name": these are in C2:C4 and in the cells highlighted in yellow. The data in these (yellow) cells is used by the INDEX functions to get the data form the main sheet.

Formula(s) in A2 down are of the form:

=IFERROR(MATCH("Sample Name",INDIRECT("'Imported R data'!$A" & B1 &":$A$100"),0),"")

I use INDIRECT to offset the range used to find the next occurrence of "Sample Name". The value in A is the relative row (position) in the range chosen.

The data in the tables uses INDEX to get the data:

=IF(INDEX('Imported R data'!$A$1:$A$100,$E$1+2+ROWS($1:1))="","",INDEX('Imported R data'!$A$1:$A$100,$E$1+2+ROWS($1:1)))

Thanks, JonTopley! I've fiddled around with your formulae a bit (changing the absolute/relative cell references) to make it easy to copy across multiple columns, and I think I'm starting to understand what the INDIRECT function is doing. I can see how that might become a valuable tool in my toolbox. Thanks again!

Thanks for the feedback.