Return many rows with OFFSET(MATCH...

[robotlust]

Hi,
I'm nearly there. i've used an OFFSET(MATCH to ruturn many rows based on a single criteria. At the moment the formula just duplicates the same row.

I've attached a spreadsheet with the current formula, and a section colored on yellow how i would like it to work.

Any help aperciated...

Cheers
Cameron

[rylo]

Cameron

Have a look at the attached file.

rylo

[robotlust]

Thanks rylo!

one question though: you have used the number 15 twice throughout the formula. Can i ask why 15 is a relevant number? Im applying this formula to reference over 300 rows on a different sheet..

Please Login or Register  to view this content.

Cheers
Cam

[rylo]

Cam

15 is used as the formula starts in row 16 so that when it is subtracted from the row number, it produces 1. As the row increases, you get an increasing series 1,2,3...

This is used in both the first part of the if statement to work out if the count of the forumula is greater than the number of items that match the criteria

Please Login or Register  to view this content.

and also as an index to the SMALL function to work out the smallest number, next smallest etc.


HTH

rylo

[robotlust]

rylo,
Thanks for the clarification.

I've just realised a potential problem with my application of this formula. I can use this formula- just no in this case
I've noticed the data must be above the formula. This is not well suited to my application of this.
Does anyone know of a different approach? Refer to the initial file attached.

Cheers
Cam

[rylo]

Cam

Sorry, don't follow. The formula filled in the table as per your example file structure, with the data above the output table.


rylo

[robotlust]

hi,
I have the data one on e sheet, some 300 rows of data, and a output sheet that returns the arguments. I, very meticiously edited the forumla to reference to another sheet and it didnt work.

[rylo]

Hi

Try this one.

rylo

[robotlust]

thank rylo. thats much better - not sure why it wouldnt work.
I've had to append a subtract at the end of the formula to take into account the data starting on row 17, not 1.