Need help with INDEX and OFFSET function

[mikehk]

this formula works :
=INDEX(CalSpecs,MATCH(A3,CalDate,0),OFFSET(Table1[[#Headers],[Specs]],1,1,1,1))

to get the 2nd row, changed formula to :
=INDEX(CalSpecs,MATCH(A3,CalDate,0),OFFSET(Table1[[#Headers],[Specs]],2,1,1,1))

but the results is same as 1
what am I doing wrong

[AliGW]

With OFFSET and table references, you need to hard code the point that the OFFSET is working from. Why do you think you need to use OFFSET here? It would be useful to see a sample workbook.

[Glenn Kennedy]

So far as I can see there is no good reason to use OFFSET and many reasons NOT to. Please supply a sample sheet.

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[mikehk]

Please find attached herewith sample sheet.

Thanks.

[Glenn Kennedy]

Use formulae similar to this in A4 to D4. Then select A4:D17 and copy paste as a block into E4, I4, etc. When starting a new row, ensure that you change the reference from A$3 to a$18, etc.

=IFERROR(INDEX(CalTime,AGGREGATE(15,6,ROW(Table1[[Time]:[Time]])/(Table1[[Date]:[Date]]=Calendar!A$3),ROWS($1:1))),"")

[mikehk]

You are a genius..thank you so much

[mikehk]

Use formulae similar to this in A4 to D4. Then select A4:D17 and copy paste as a block into E4, I4, etc. When starting a new row, ensure that you change the reference from A$3 to a$18, etc.

=IFERROR(INDEX(CalTime,AGGREGATE(15,6,ROW(Table1[[Time]:[Time]])/(Table1[[Date]:[Date]]=Calendar!A$3),ROWS($1:1))),"")

I copy pasted the formulas and changed the reference from A$3 to A$18, what else do I need to change
as I am unable to get it work
Please see the attached file.

Thanks.

[Bo_Ry]

Change in Blue

A19

=IFERROR(INDEX(CalTime,AGGREGATE(15,6,ROW(Table1[[Time]:[Time]])/(Table1[[Date]:[Date]]=A$18),ROWS($1:1))),"")

[Glenn Kennedy]

Yes. My mistake:

IFERROR(INDEX(CalTime,AGGREGATE(15,6,ROW(Table1[[Time]:[Time]])/(Table1[[Date]:[Date]]=Calendar!A$18),ROWS($1:1))),"")

[mikehk]

Got it....thanks a lot

[Glenn Kennedy]

It's basically just a counter. I forgot to re-set it to start again at 1...