Using Index across several columns

[HammerJoe]

Hi,

I have this table that span from colum E:J

1-4 14% 113-116 42% 225-228 71%
5-8 15% 117-120 43% 229-232 72%
9-12 16% 121-124 44% 233-236 73%
13-16 17% 125-128 45% 237-240 74%
The table is complete for 365 days, I just omited the rest to save
space.

Cell A1 has number of days.
I want to find the percentage that matches that day.
I have come up with this formula:
=INDEX(F1:F28,ROUNDUP(A1/4,0))

Which works fine, IF the number of days is less than 16 which is
normal because index only looks in column F.
I would like to expand this formula to also look at colum h and J
based on A1 value?
Any help appreciated,

PS: I could just have this table in one single column, but this table
is copied from a webpage and pasted into the worksheet regularly and I
just want to avoid the hassle of recreating the single column table.

Thanks.

 

[carlo]

Hey Joe

I tried to rebuild your table, but i only arrived at 333-336, and you
said it goes until 365, so maybe some adjustments to my formula need
to be done:

=OFFSET(A1,ROUNDUP((A1-FLOOR((A1-1)/112,1)*112)/4,0)-1,FLOOR((A1-1)/
112,1)*2+5)

hth

Carlo

 

[HammerJoe]

Thank you Carlo for your help. The formula works.

I see what you mean about dates above 336.
Here is the final data of the table on column I16:J28
285-292 87%
293-296 88%
297-300 89%
301-308 91%
309-312 92%
313-316 93%
317-320 94%
321-325 95%
326-330 96%
331-335 97%
336-340 98%
341-345 99%
346-365 100%

Actually I just noticed it. Interesting
The table becomes all funky towards the end of the year.
And this is the problem, Next week these date ranges could be
different.
Is it possible then to find where the A1 date falls in and pick the
value on the column to the right? That would solve the problem!
The days on the table are in text mode.

 

[carlo]

Well....that was interesting
I learned a lot today.

With following constellation it works for me:
A1: Your Day
A2: Your Number of Rows per column (could be hardcoded as well)
B2: don't panic...it's a loooong formula, and you have to enter it
with ctrl+shift+enter!!!!
=SUM((LEFT(E1:E28,FIND("-",E1:E28)-1)*1<A1+1)*1)+SUM((LEFT(G1:G28,FIND("-",G1:G28)-1)*1<A1+1)*1)+SUM((LEFT(I1:I28,FIND("-",I1:I28)-1)*1<A1+1)*1)
B1: the actual output:
=OFFSET(A1,MOD(B2-1,A2),FLOOR((B2-1)/A2,1)*2+5)

maybe somebody can shorten my B2 formula, i didn't find a way to look
at different ranges at the same time, that's why i had to add 3 times
the same formula with different ranges. Would be glad if someone could
help me out there.

Cheers
Carlo