| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX FORMULAS & MULTIPLE TABLES
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX FORMULAS & MULTIPLE TABLES
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX FORMULAS & MULTIPLE TABLES
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX FORMULAS & MULTIPLE TABLES
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX FORMULAS & MULTIPLE TABLES
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX FORMULAS & MULTIPLE TABLES
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX FORMULAS & MULTIPLE TABLES
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'!B3:D6,'COMP VAN'!G3:I6,'COMP VAN'!L3:N6),MATCH(F3,'COMP VAN'!A3:A6,0),MATCH(QUOTATION!C4,'COMP VAN'!B2:D2,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) B28:D48 & 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX FORMULAS & MULTIPLE TABLES
Hi Aaron,
You could do something like this =IF(E3="COMP",INDEX(CHOOSE(QUOTATION!C3,'COMP VAN'!B3:D6,'COMP VAN'!G3:I6,'COMP VAN'!L3:N6),MATCH(F3,'COMP VAN'!A3:A6,0),MATCH(QUOTATION!C4,'COMP VAN'!B2:D2,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'!B3:D6,'COMP VAN'!G3:I6,'COMP VAN'!L3:N6),MATCH(F3,'COMP VAN'!A3:A6,0),MATCH(QUOTATION!C4,'COMP VAN'!B2:D2,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) B28:D48 & 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX FORMULAS & MULTIPLE TABLES
Great advice, thanks again Charles, I used the below formula:
=IF(E4="COMP",INDEX(CHOOSE(QUOTATION!C3,'COMP VAN'!B3:D6,'COMP VAN'!G3:I6,'COMP VAN'!L3:N6),MATCH(F4,'COMP VAN'!A3:A6,0),MATCH(QUOTATION!C4,'COMP VAN'!B2:D2,0)))+IF(E4="TPFT",INDEX(CHOOSE(QUOTATIO N!C3,'COMP VAN'!B13:D16,'COMP VAN'!G13:I16,'COMP VAN'!L13:N16),MATCH(F4,'COMP VAN'!A13:A16,0),MATCH(QUOTATION!C4,'COMP VAN'!B12:D12,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'!B3:D6,'COMP VAN'!G3:I6,'COMP VAN'!L3:N6),MATCH(F3,'COMP VAN'!A3:A6,0),MATCH(QUOTATION!C4,'COMP VAN'!B2:D2,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'!B3:D6,'COMP VAN'!G3:I6,'COMP VAN'!L3:N6),MATCH(F3,'COMP VAN'!A3:A6,0),MATCH(QUOTATION!C4,'COMP VAN'!B2:D2,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) B28:D48 & 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| using 3 tables in one spreadsheet and utilising index formula to retrieve information | Excel Worksheet Functions | |||
| Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
| SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec | Excel Discussion (Misc queries) | |||
| summating vlookup or index formulas | Excel Worksheet Functions | |||
| index/ match formulas orice | Excel Worksheet Functions | |||
Linear Mode
