using 3 tables in one spreadsheet and utilising index formula to retrieve information
 

Hello everyone,

I have a table set up for which is use the below code to retrieve data,
depending on the info I insert:

=INDEX(F3:H22,MATCH(B8,E3:E22,0),MATCH(B2,F2:H2,0) )

This works a treat, however, I want to use more than one table for the data
retrieval. ie: cell A1 will have a choice of the user inserting 1,2 or 3
for selection of the 3 tables I have created that will range J2:M22 &
O2:R22, whilst B2 & B8 dictate the row and column required for all. The
column and rows of all three tables will be the same so this can be achieved
ie: A,B,C going across and 1 through 20 going down.

Does this make sense? I hope so. I am fairly new to indexes etc.

Thanks again in advance.

Aaron

using 3 tables in one spreadsheet and utilising index formula to retrieve information
 

Hi Aaron

Try
=OFFSET(INDEX(F3:H22,MATCH(B8,E3:E22,0),
MATCH(B2,F2:H2,0)),0,($A$1-1)*5)

--
Regards
Roger Govier



"Aaron Hodson (Coversure)" wrote in message
...
Hello everyone,

I have a table set up for which is use the below code to retrieve data,
depending on the info I insert:

=INDEX(F3:H22,MATCH(B8,E3:E22,0),MATCH(B2,F2:H2,0) )

This works a treat, however, I want to use more than one table for the
data retrieval. ie: cell A1 will have a choice of the user inserting 1,2
or 3 for selection of the 3 tables I have created that will range J2:M22 &
O2:R22, whilst B2 & B8 dictate the row and column required for all. The
column and rows of all three tables will be the same so this can be
achieved ie: A,B,C going across and 1 through 20 going down.

Does this make sense? I hope so. I am fairly new to indexes etc.

Thanks again in advance.

Aaron

using 3 tables in one spreadsheet and utilising index formula to retrieve information
 

Sorry Roger, I can't get this to work.

I am going to post another explanation now I am slowly trying to get my head
around the problem

Thanks

"Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message
...
Hi Aaron

Try
=OFFSET(INDEX(F3:H22,MATCH(B8,E3:E22,0),
MATCH(B2,F2:H2,0)),0,($A$1-1)*5)

--
Regards
Roger Govier



"Aaron Hodson (Coversure)" wrote in message
...
Hello everyone,

I have a table set up for which is use the below code to retrieve data,
depending on the info I insert:

=INDEX(F3:H22,MATCH(B8,E3:E22,0),MATCH(B2,F2:H2,0) )

This works a treat, however, I want to use more than one table for the
data retrieval. ie: cell A1 will have a choice of the user inserting
1,2 or 3 for selection of the 3 tables I have created that will range
J2:M22 & O2:R22, whilst B2 & B8 dictate the row and column required for
all. The column and rows of all three tables will be the same so this
can be achieved ie: A,B,C going across and 1 through 20 going down.

Does this make sense? I hope so. I am fairly new to indexes etc.

Thanks again in advance.

Aaron