INDEX FORMULAS & MULTIPLE TABLES

[Aaron Hodson \(Coversure\)]

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

 

[Max]

One way

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

 

[Aaron Hodson \(Coversure\)]

Thank you Max,

This works perfectly.

Kind regards

Aaron

 

[Max]

welcome, Aaron.

 

[Charles Williams]

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\)]

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]

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\)]

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]

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\)]

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(QUOTATION!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