4 criteria Index Match Lookup table

[we_B_jamin]

Hello All,

I am new to Mr. Excel, and I have usually been able to solve issues without assistance but alas, my spreadsheets are getting more complex.

To keep it brief:

I need to look up a table of life expediencies, information in the table is organized as follows:

[TABLE="width: 900"]
<colgroup><col><col span="4"><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD="colspan: 5"]NEW Life Expectancy Table[/TD]
[TD="colspan: 5"]OLD Life Expectancy Table[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Non Smoker[/TD]
[TD="colspan: 2"]Smoker[/TD]
[TD] [/TD]
[TD="colspan: 2"]Non Smoker[/TD]
[TD="colspan: 2"]Smoker[/TD]
[/TR]
[TR]
[TD]Age[/TD]
[TD]Male[/TD]
[TD]Female[/TD]
[TD]Male[/TD]
[TD]Female[/TD]
[TD]Age Nearest[/TD]
[TD]Male Non Smoker[/TD]
[TD]Female Non Smoker[/TD]
[TD]Male Smoker[/TD]
[TD]Female Smoker[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]67[/TD]
[TD]15[/TD]
[TD]64[/TD]
[TD]68[/TD]
[TD]56[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]66[/TD]
[TD]16[/TD]
[TD]63[/TD]
[TD]67[/TD]
[TD]55[/TD]
[TD]62[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]65[/TD]
[TD]17[/TD]
[TD]62[/TD]
[TD]66[/TD]
[TD]54[/TD]
[TD]61[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]64[/TD]
[TD]18[/TD]
[TD]61[/TD]
[TD]65[/TD]
[TD]53[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]63[/TD]
[TD]19[/TD]
[TD]60[/TD]
[TD]64[/TD]
[TD]52[/TD]
[TD]59
[/TD]
[/TR]
</tbody>[/TABLE]

A, B, C etc.. are the columns (ignore "F" as it will be removed)
Basically I have 3x drop down lists.
1) Table: New vs. Old
2) Status: Non Smoker vs. Smoker
3) Sex: Male vs. Female
The last input is the age, which corresponds to column A (input cell)

I can't figure out how to do a nice, clean, index & match. I though of creating a reference table with a vlookup to select the column.

Any ideas

Thanks for your help.

 

[PaddyD]

Any reason you can't reorganise your data into a single table:

OldvNew| Gender | Smoker | Age | Expectancy

??

 

[we_B_jamin]

It is in one large table.. I am not sure how this helps. Could you please elaborate?

 

[robin33]

Can anybody help me to find a specific sequence of numbers in given data?

eg: in a data (in column) i want to find and highlight cells (in column) containing sequence 2001 which is repeated throughout the column at irregular intervals. Thanks!
1
1
1
2
2
1
2
5
3
3
3
2
5
6
7
8
6
2
0
0
1
2
1
0
0
2
0
0
1
2
1
0
0