Obtain multiple results for a given value from a table - (was Offset Formula?)

Hello,

I have this formula which gives the matching result from column J on Sheet 2.

=IFERROR(INDEX(Sheet2!$J$3:$J$12,MATCH($A$3,Sheet2!$D$3:$D$12,0)),"")

This formula is in Sheet 1 cell C6.

What would the formulas be for C7, C8, and C9 please? I would like the next data from column J on Sheet 2 matching what is put in $A$3.

Thank you,
XJ

Please note that your thread title is meant to summarise what you are trying to do, not what you think the solution might be (which may or may not be apt). Please update your thread title accordingly. Thank you.

Try this:

=IFERROR(INDEX(Sheet2!$J$3:$J$14,MATCH($A$3,Sheet2!$D$3:$D$14,0)+ROWS(C$6:C6)-1),"")

Non-volatile, so it doesn't recalculate all the time!!

As the thread is marked as solved, I will not delete the solution. However, please note that thread titles need to be more explicit of your problem in future, and if you are asked to change a thread title, you should do so. You must NOT ignore moderation requests.

Thanks - please remember this in future.

Originally Posted by AliGW

You must NOT ignore moderation requests.

I didn't see it!!

Don't worry I am leaving

That's rather an extreme response to being asked to amend your thread title, especially as I also explained why it needed to be done. Your prerogative, of course, but there really is no need for you to leave.

Originally Posted by Glenn Kennedy

I didn't see it!!

Wasn't aimed at you, Glenn, and the OP has now amended the thread title.

WHy not save yourself a whole lot of trouble and create a proper normalised database.

Add a Pivot Table and some Slicers and you have a much more efficient analysis system

See attached

Hi xJohnson,

I would encourage you not to leave. No one is trying to be unhelpful. Sometimes misunderstandings occur.

Actually I changed the title on behalf of the OP.

Thanks for the clarification, Richard. I was working from the new rules (rule #1 and footnote A, regarding experienced members), which is why I assumed (incorrectly) that the OP had complied with my request.

Originally Posted by AliGW

Thanks for the clarification, Richard. I was working from the new rules (rule #1 and footnote A, regarding experienced members), which is why I assumed (incorrectly) that the OP had complied with my request.

No problem. That's why we're here