INDEX FORMULAS & MULTIPLE TABLES

Hello everyone,

I am using the below formula to capture data from a table in my spreadsheet:

=INDEX(F3:H22,MATCH(B9,E3:E22,0),MATCH(B3,F2:H2,0) )

Within the spreadsheet I have an additional 2 tables with data range K3:M22
and P3:R22.

I want to be able to enter either 1 2 or 3 into cell B2..... Depending on
the reply; 1 will take data from F3:H22 - 2 will take data from table
K3:M22 and - 3 will take data from P3:R22

Thank you all in anticipation of your help.

Kind regards

Aaron

One way

Try:
=OFFSET(INDIRECT(INDEX({"E2";"J2";"O2"},B2)),MATCH (B9,E3:E22,0),MATCH(B3,F2:H2,0))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aaron Hodson (Coversure)" wrote in message
...
Hello everyone,

I am using the below formula to capture data from a table in my
spreadsheet:

=INDEX(F3:H22,MATCH(B9,E3:E22,0),MATCH(B3,F2:H2,0) )

Within the spreadsheet I have an additional 2 tables with data range
K3:M22 and P3:R22.

I want to be able to enter either 1 2 or 3 into cell B2..... Depending on
the reply; 1 will take data from F3:H22 - 2 will take data from table
K3:M22 and - 3 will take data from P3:R22

Thank you all in anticipation of your help.

Kind regards

Aaron

Thank you Max,

This works perfectly.

Kind regards

Aaron

"Max" wrote in message
...
One way

Try:
=OFFSET(INDIRECT(INDEX({"E2";"J2";"O2"},B2)),MATCH (B9,E3:E22,0),MATCH(B3,F2:H2,0))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aaron Hodson (Coversure)" wrote in message
...
Hello everyone,

I am using the below formula to capture data from a table in my
spreadsheet:

=INDEX(F3:H22,MATCH(B9,E3:E22,0),MATCH(B3,F2:H2,0) )

Within the spreadsheet I have an additional 2 tables with data range
K3:M22 and P3:R22.

I want to be able to enter either 1 2 or 3 into cell B2..... Depending on
the reply; 1 will take data from F3:H22 - 2 will take data from
table K3:M22 and - 3 will take data from P3:R22

Thank you all in anticipation of your help.

Kind regards

Aaron

welcome, Aaron.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aaron Hodson (Coversure)" wrote in message
...
Thank you Max,

This works perfectly.

Kind regards

Aaron

Another (non-volatile) way using CHOOSE:

=INDEX(CHOOSE(B2,F3:H22,K3:M22,P3:R22),MATCH(B9,E3 :E22,0),MATCH(B3,F2:H2,0))


Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

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

I am using the below formula to capture data from a table in my
spreadsheet:

=INDEX(F3:H22,MATCH(B9,E3:E22,0),MATCH(B3,F2:H2,0) )

Within the spreadsheet I have an additional 2 tables with data range
K3:M22 and P3:R22.

I want to be able to enter either 1 2 or 3 into cell B2..... Depending on
the reply; 1 will take data from F3:H22 - 2 will take data from table
K3:M22 and - 3 will take data from P3:R22

Thank you all in anticipation of your help.

Kind regards

Aaron

Thanks Charles,

I have used both formulas, both work perfectly. Thanks.

The 'offset' formula made the spreadsheet crash every now and again, whereas
the 'choose' formula no such crashes have happened.

I suppose the crashes could be because I am using XP office on a vista
machine?

Thanks again,

Aaron

"Charles Williams" wrote in message
...
Another (non-volatile) way using CHOOSE:

=INDEX(CHOOSE(B2,F3:H22,K3:M22,P3:R22),MATCH(B9,E3 :E22,0),MATCH(B3,F2:H2,0))


Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

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

I am using the below formula to capture data from a table in my
spreadsheet:

=INDEX(F3:H22,MATCH(B9,E3:E22,0),MATCH(B3,F2:H2,0) )

Within the spreadsheet I have an additional 2 tables with data range
K3:M22 and P3:R22.

I want to be able to enter either 1 2 or 3 into cell B2..... Depending on
the reply; 1 will take data from F3:H22 - 2 will take data from
table K3:M22 and - 3 will take data from P3:R22

Thank you all in anticipation of your help.

Kind regards

Aaron

The OFFSET(INDIRECT( formula should not crash, but it certainly makes Excel
work a lot harder!

Personally I try to avoid INDIRECT wherever possible.

Charles
_______________________________
UK Cambridge XL Users Conference
http://www.exceluserconference.com/UKEUC.html

"Aaron Hodson (Coversure)" wrote in message
...
Thanks Charles,

I have used both formulas, both work perfectly. Thanks.

The 'offset' formula made the spreadsheet crash every now and again,
whereas the 'choose' formula no such crashes have happened.

I suppose the crashes could be because I am using XP office on a vista
machine?

Thanks again,

Aaron

"Charles Williams" wrote in message
...
Another (non-volatile) way using CHOOSE:

=INDEX(CHOOSE(B2,F3:H22,K3:M22,P3:R22),MATCH(B9,E3 :E22,0),MATCH(B3,F2:H2,0))


Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

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

I am using the below formula to capture data from a table in my
spreadsheet:

=INDEX(F3:H22,MATCH(B9,E3:E22,0),MATCH(B3,F2:H2,0) )

Within the spreadsheet I have an additional 2 tables with data range
K3:M22 and P3:R22.

I want to be able to enter either 1 2 or 3 into cell B2..... Depending
on the reply; 1 will take data from F3:H22 - 2 will take data from
table K3:M22 and - 3 will take data from P3:R22

Thank you all in anticipation of your help.

Kind regards

Aaron

Thanks Charles,

I wonder whether you could help me again with a further formula.

I have been creating (what I think) is a complicated rating table.

I am on the last leg....

I am using the below formula:

=INDEX(CHOOSE(QUOTATION!C3,'COMP VAN'!B36,'COMP VAN'!G3:I6,'COMP
VAN'!L3:N6),MATCH(F3,'COMP VAN'!A3:A6,0),MATCH(QUOTATION!C4,'COMP
VAN'!B22,0))

This works perfectly, however, cell E3 will have to options for the user to
type: 'COMP' OR 'TPFT'. At present the above tables where the data is
retrieved is the comp tables which is fine, however, if cell E3 states TPFT
I would like to retreive data from tables: (data range) B2848 & G28:I48 &
L28:N48.

Does this make sense? at present as you can tell from the above formula that
I currently use, 'COMP' retreives information from 3 tables depending on the
answer inputted in C3.

Thanks in anticipation.

Kind regards

Aaron


"Charles Williams" wrote in message
...
The OFFSET(INDIRECT( formula should not crash, but it certainly makes
Excel work a lot harder!

Personally I try to avoid INDIRECT wherever possible.

Charles
_______________________________
UK Cambridge XL Users Conference
http://www.exceluserconference.com/UKEUC.html

"Aaron Hodson (Coversure)" wrote in message
...
Thanks Charles,

I have used both formulas, both work perfectly. Thanks.

The 'offset' formula made the spreadsheet crash every now and again,
whereas the 'choose' formula no such crashes have happened.

I suppose the crashes could be because I am using XP office on a vista
machine?

Thanks again,

Aaron

"Charles Williams" wrote in message
...
Another (non-volatile) way using CHOOSE:

=INDEX(CHOOSE(B2,F3:H22,K3:M22,P3:R22),MATCH(B9,E3 :E22,0),MATCH(B3,F2:H2,0))


Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

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

I am using the below formula to capture data from a table in my
spreadsheet:

=INDEX(F3:H22,MATCH(B9,E3:E22,0),MATCH(B3,F2:H2,0) )

Within the spreadsheet I have an additional 2 tables with data range
K3:M22 and P3:R22.

I want to be able to enter either 1 2 or 3 into cell B2..... Depending
on the reply; 1 will take data from F3:H22 - 2 will take data from
table K3:M22 and - 3 will take data from P3:R22

Thank you all in anticipation of your help.

Kind regards

Aaron

Hi Aaron,

You could do something like this

=IF(E3="COMP",INDEX(CHOOSE(QUOTATION!C3,'COMP VAN'!B36,'COMP
VAN'!G3:I6,'COMP
VAN'!L3:N6),MATCH(F3,'COMP VAN'!A3:A6,0),MATCH(QUOTATION!C4,'COMP
VAN'!B22,0)),
similar INDEX(CHOOSE .. MATCH.. MATCH formula but for TPFT)

or you could extend the CHOOSE to select from 6 ranges:
CHOOSE(QUOTATION!C3+IF(E3="TPFT",3,0), Comp tables ... TPFT tables)
and you might have to also add a CHOOSE or an IF inside the MATCH functions
to select the range to do the MATCH on, depending on E3.

Charles
_______________________________
UK Cambridge XL Users Conference
http://www.exceluserconference.com/UKEUC.html




"Aaron Hodson (Coversure)" wrote in message
...
Thanks Charles,

I wonder whether you could help me again with a further formula.

I have been creating (what I think) is a complicated rating table.

I am on the last leg....

I am using the below formula:

=INDEX(CHOOSE(QUOTATION!C3,'COMP VAN'!B36,'COMP VAN'!G3:I6,'COMP
VAN'!L3:N6),MATCH(F3,'COMP VAN'!A3:A6,0),MATCH(QUOTATION!C4,'COMP
VAN'!B22,0))

This works perfectly, however, cell E3 will have to options for the user
to type: 'COMP' OR 'TPFT'. At present the above tables where the data is
retrieved is the comp tables which is fine, however, if cell E3 states
TPFT I would like to retreive data from tables: (data range) B2848 &
G28:I48 & L28:N48.

Does this make sense? at present as you can tell from the above formula
that I currently use, 'COMP' retreives information from 3 tables depending
on the answer inputted in C3.

Thanks in anticipation.

Kind regards

Aaron


"Charles Williams" wrote in message
...
The OFFSET(INDIRECT( formula should not crash, but it certainly makes
Excel work a lot harder!

Personally I try to avoid INDIRECT wherever possible.

Charles
_______________________________
UK Cambridge XL Users Conference
http://www.exceluserconference.com/UKEUC.html

"Aaron Hodson (Coversure)" wrote in message
...
Thanks Charles,

I have used both formulas, both work perfectly. Thanks.

The 'offset' formula made the spreadsheet crash every now and again,
whereas the 'choose' formula no such crashes have happened.

I suppose the crashes could be because I am using XP office on a vista
machine?

Thanks again,

Aaron

"Charles Williams" wrote in message
...
Another (non-volatile) way using CHOOSE:

=INDEX(CHOOSE(B2,F3:H22,K3:M22,P3:R22),MATCH(B9,E3 :E22,0),MATCH(B3,F2:H2,0))


Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

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

I am using the below formula to capture data from a table in my
spreadsheet:

=INDEX(F3:H22,MATCH(B9,E3:E22,0),MATCH(B3,F2:H2,0) )

Within the spreadsheet I have an additional 2 tables with data range
K3:M22 and P3:R22.

I want to be able to enter either 1 2 or 3 into cell B2..... Depending
on the reply; 1 will take data from F3:H22 - 2 will take data
from table K3:M22 and - 3 will take data from P3:R22

Thank you all in anticipation of your help.

Kind regards

Aaron

Great advice, thanks again Charles, I used the below formula:

=IF(E4="COMP",INDEX(CHOOSE(QUOTATION!C3,'COMP VAN'!B36,'COMP
VAN'!G3:I6,'COMP VAN'!L3:N6),MATCH(F4,'COMP
VAN'!A3:A6,0),MATCH(QUOTATION!C4,'COMP
VAN'!B22,0)))+IF(E4="TPFT",INDEX(CHOOSE(QUOTATIO N!C3,'COMP
VAN'!B1316,'COMP VAN'!G13:I16,'COMP VAN'!L13:N16),MATCH(F4,'COMP
VAN'!A13:A16,0),MATCH(QUOTATION!C4,'COMP VAN'!B1212,0)))

The more I look at these formulas the more they start to make sense.

A great feature, I feel, of excel is that if I 'copy' & 'paste' the formula
into another cell (for multiple calcs), when I select the formula in the
formula bar, I can move the highlighted boxes in the spreadsheet to alter
the formula automatically.

Aaron

"Charles Williams" wrote in message
...
Hi Aaron,

You could do something like this

=IF(E3="COMP",INDEX(CHOOSE(QUOTATION!C3,'COMP VAN'!B36,'COMP
VAN'!G3:I6,'COMP
VAN'!L3:N6),MATCH(F3,'COMP VAN'!A3:A6,0),MATCH(QUOTATION!C4,'COMP
VAN'!B22,0)),
similar INDEX(CHOOSE .. MATCH.. MATCH formula but for TPFT)

or you could extend the CHOOSE to select from 6 ranges:
CHOOSE(QUOTATION!C3+IF(E3="TPFT",3,0), Comp tables ... TPFT tables)
and you might have to also add a CHOOSE or an IF inside the MATCH
functions to select the range to do the MATCH on, depending on E3.

Charles
_______________________________
UK Cambridge XL Users Conference
http://www.exceluserconference.com/UKEUC.html




"Aaron Hodson (Coversure)" wrote in message
...
Thanks Charles,

I wonder whether you could help me again with a further formula.

I have been creating (what I think) is a complicated rating table.

I am on the last leg....

I am using the below formula:

=INDEX(CHOOSE(QUOTATION!C3,'COMP VAN'!B36,'COMP VAN'!G3:I6,'COMP
VAN'!L3:N6),MATCH(F3,'COMP VAN'!A3:A6,0),MATCH(QUOTATION!C4,'COMP
VAN'!B22,0))

This works perfectly, however, cell E3 will have to options for the user
to type: 'COMP' OR 'TPFT'. At present the above tables where the data is
retrieved is the comp tables which is fine, however, if cell E3 states
TPFT I would like to retreive data from tables: (data range) B2848 &
G28:I48 & L28:N48.

Does this make sense? at present as you can tell from the above formula
that I currently use, 'COMP' retreives information from 3 tables
depending on the answer inputted in C3.

Thanks in anticipation.

Kind regards

Aaron


"Charles Williams" wrote in message
...
The OFFSET(INDIRECT( formula should not crash, but it certainly makes
Excel work a lot harder!

Personally I try to avoid INDIRECT wherever possible.

Charles
_______________________________
UK Cambridge XL Users Conference
http://www.exceluserconference.com/UKEUC.html

"Aaron Hodson (Coversure)" wrote in message
...
Thanks Charles,

I have used both formulas, both work perfectly. Thanks.

The 'offset' formula made the spreadsheet crash every now and again,
whereas the 'choose' formula no such crashes have happened.

I suppose the crashes could be because I am using XP office on a vista
machine?

Thanks again,

Aaron

"Charles Williams" wrote in message
...
Another (non-volatile) way using CHOOSE:

=INDEX(CHOOSE(B2,F3:H22,K3:M22,P3:R22),MATCH(B9,E3 :E22,0),MATCH(B3,F2:H2,0))


Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

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

I am using the below formula to capture data from a table in my
spreadsheet:

=INDEX(F3:H22,MATCH(B9,E3:E22,0),MATCH(B3,F2:H2,0) )

Within the spreadsheet I have an additional 2 tables with data range
K3:M22 and P3:R22.

I want to be able to enter either 1 2 or 3 into cell B2.....
Depending on the reply; 1 will take data from F3:H22 - 2 will
take data from table K3:M22 and - 3 will take data from P3:R22

Thank you all in anticipation of your help.

Kind regards

Aaron