Index formula

mattbird

Active Member
Joined
Oct 15, 2013
Messages
268
Hi,

I am have a table (Excel 2003) that I would like to return an answer from another table, but it needs to take into consideration gender and age. I have written a Index formula that works for age 0-29 male & Female, but am now stuck as I can't work out how to make it return an answer is the person is ie 30-34, 35-39, 40-44, 45-49, 50-54, 55-59, 60-64 as well as taking their gender into consideration.

The formula I am working with is:
Code:
=IF($F312="M",(IF(P312=0,0,IF(E312<=30,IF(P312>=8.25,(INDEX('PFA Conv Table - 1.5 Mile Run'!$A5:$A55,MATCH(P312,'PFA Conv Table - 1.5 Mile Run'!$B5:$B55))),100)))),(IF(P312=0,0,(IF(P312>=10,(INDEX('PFA Conv Table - 1.5 Mile Run'!$A5:$A55,MATCH(P312,'PFA Conv Table - 1.5 Mile Run'!$F5:$F55))),100)))))

It is frustrating as everything I have tried doesn't work.

Any help would be greatly appreciated.

Matt
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,213
use a vlookup table for this kind of job to determine the years.
 

mattbird

Active Member
Joined
Oct 15, 2013
Messages
268
Hi,

I have also got stuck at the same point trying with a vlookup, but I read that the index formula is better that a vlookup as it is quicker.

regards
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Hi,

I am have a table (Excel 2003) that I would like to return an answer from another table, but it needs to take into consideration gender and age. I have written a Index formula that works for age 0-29 male & Female, but am now stuck as I can't work out how to make it return an answer is the person is ie 30-34, 35-39, 40-44, 45-49, 50-54, 55-59, 60-64 as well as taking their gender into consideration.

The formula I am working with is:
Code:
=IF($F312="M",(IF(P312=0,0,IF(E312<=30,IF(P312>=8.25,(INDEX('PFA Conv Table - 1.5 Mile Run'!$A5:$A55,MATCH(P312,'PFA Conv Table - 1.5 Mile Run'!$B5:$B55))),100)))),(IF(P312=0,0,(IF(P312>=10,(INDEX('PFA Conv Table - 1.5 Mile Run'!$A5:$A55,MATCH(P312,'PFA Conv Table - 1.5 Mile Run'!$F5:$F55))),100)))))

It is frustrating as everything I have tried doesn't work.

Any help would be greatly appreciated.

Matt

Please provide an example of your table(s) as well as detailing the result you would like to see.

Matty
 

mattbird

Active Member
Joined
Oct 15, 2013
Messages
268

ADVERTISEMENT

Thanks for your help.

I am not sure how insert a copy of my table on here?

Matt
 

mattbird

Active Member
Joined
Oct 15, 2013
Messages
268

ADVERTISEMENT

Matty,

Table 1 is the input table and Table 2 (next post) is where it looks for the result.

Table 1

Excel 2007
DEFGHIJKLMNOPQRST
11DOBAGEM/FSQNDate ofDate of NextPRESS - UPSSIT - UPS2.4km TIMED RUN
12COYAssessment AssessmentSCOREPOINTSRESULTSCOREPOINTSRESULTD.MINMINSECPOINTSRESULT
13
1411/04/9025M10.50103070P
150.00
160.00
170.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
PFA

Worksheet Formulas
CellFormula
S14=IF($F14="M",(IF(P14=0,0,IF(E14<=30,IF(P14>=8.25,(INDEX('PFA Conv Table - 1.5 Mile Run'!$A3:$A53,MATCH(P14,'PFA Conv Table - 1.5 Mile Run'!$B3:$B53))),100)))),(IF(P14=0,0,(IF(P14>=10,(INDEX('PFA Conv Table - 1.5 Mile Run'!$A3:$A53,MATCH(P14,'PFA Conv Table - 1.5 Mile Run'!$F3:$F53))),100)))))
T14=IF($P14=0,"",(IF($F14="M",(IF(P14<=10.5,"P","F")),IF(P14<=13,"P","F"))))
E14=IF(D14="","",DATEDIF(D14,NOW(),"Y"))
E15=IF(D15="","",DATEDIF(D15,NOW(),"Y"))
E16=IF(D16="","",DATEDIF(D16,NOW(),"Y"))
E17=IF(D17="","",DATEDIF(D17,NOW(),"Y"))
I14=IF(H14="","",DATE(YEAR(H14),MONTH(H14)+IF(Y14="Fail",0,6),DAY(H14)+IF(Y14="Fail",7,0)))
I15=IF(H15="","",DATE(YEAR(H15),MONTH(H15)+IF(Y15="Fail",0,6),DAY(H15)+IF(Y15="Fail",7,0)))
I16=IF(H16="","",DATE(YEAR(H16),MONTH(H16)+IF(Y16="Fail",0,6),DAY(H16)+IF(Y16="Fail",7,0)))
I17=IF(H17="","",DATE(YEAR(H17),MONTH(H17)+IF(Y17="Fail",0,6),DAY(H17)+IF(Y17="Fail",7,0)))
P14=$Q14+(R14/60)
P15=$Q15+(R15/60)
P16=$Q16+(R16/60)
P17=$Q17+(R17/60)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
PF=List!$E$2:$E$3
PFA=List!#REF!

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Table 2 in next post

Matt
 
Last edited:

mattbird

Active Member
Joined
Oct 15, 2013
Messages
268
Table 2

Excel 2007
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBN
3Age Group< 3030 - 3435 - 3940 - 4445 - 4950 - 5455 - 5960 - 64Age Group
4PointsRange floorD.minsMRange floorD.minsFRange floorD.minsMRange floorD.minsFRange floorD.minsMRange floorD.minsFRange floorD.minsMRange floorD.minsFRange floorD.minsMRange floorD.minsFRange floorD.minsMRange floorD.minsFRange floorD.minsMRange floorD.minsFRange floorD.minsMRange floorD.minsFPoints
510008.250815010.00100008.500830010.50103009.000900011.00110009.250915011.50113009.500930012.001200010.001000012.751245010.501030013.501330010.751045013.831350100
6998.278.32081910.0210.1010068.528.58083510.5210.6010369.029.08090511.0211.1011069.279.33092011.5211.6011369.529.60093612.0212.10120610.0210.12100712.7712.85125110.5210.62103713.5213.60133610.7710.87105213.8513.93135699

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
PFA Conv Table - 1.5 Mile Run

Worksheet Formulas
CellFormula
C5=$D5+($E5/60)
G5=$H5+($I5/60)
K5=$L5+($M5/60)
O5=$P5+($Q5/60)
S5=$T5+($U5/60)
W5=$X5+($Y5/60)
AA5=$AB5+($AC5/60)
AE5=$AF5+($AG5/60)
AI5=$AJ5+($AK5/60)
AM5=$AN5+($AO5/60)
AQ5=$AR5+($AS5/60)
AU5=$AV5+($AW5/60)
AY5=$AZ5+($BA5/60)
BC5=$BD5+($BE5/60)
BG5=$BH5+($BI5/60)
BK5=$BL5+($BM5/60)
B6=$D5+(($E5+1)/60)
C6=$D6+($E6/60)
F6=$H5+(($I5+1)/60)
G6=$H6+($I6/60)
J6=$L5+(($M5+1)/60)
K6=$L6+($M6/60)
N6=$P5+(($Q5+1)/60)
O6=$P6+($Q6/60)
R6=$T5+(($U5+1)/60)
S6=$T6+($U6/60)
V6=$X5+(($Y5+1)/60)
W6=$X6+($Y6/60)
Z6=$AB5+(($AC5+1)/60)
AA6=$AB6+($AC6/60)
AD6=$AF5+(($AG5+1)/60)
AE6=$AF6+($AG6/60)
AH6=$AJ5+(($AK5+1)/60)
AI6=$AJ6+($AK6/60)
AL6=$AN5+(($AO5+1)/60)
AM6=$AN6+($AO6/60)
AP6=$AR5+(($AS5+1)/60)
AQ6=$AR6+($AS6/60)
AT6=$AV5+(($AW5+1)/60)
AU6=$AV6+($AW6/60)
AX6=$AZ5+(($BA5+1)/60)
AY6=$AZ6+($BA6/60)
BB6=$BD5+(($BE5+1)/60)
BC6=$BD6+($BE6/60)
BF6=$BH5+(($BI5+1)/60)
BG6=$BH6+($BI6/60)
BJ6=$BL5+(($BM5+1)/60)
BK6=$BL6+($BM6/60)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



The table points go down to 50 anything after is zero points
 
Last edited:

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Matty,

Table 1 is the input table and Table 2 (next post) is where it looks for the result.

Table 1

Excel 2007
DEFGHIJKLMNOPQRST
11DOBAGEM/FSQNDate ofDate of NextPRESS - UPSSIT - UPS2.4km TIMED RUN
12COYAssessmentAssessmentSCOREPOINTSRESULTSCOREPOINTSRESULTD.MINMINSECPOINTSRESULT
13
1411/04/9025M10.50103070P
150.00
160.00
170.00

<tbody>
</tbody>
PFA
Table 2 in next post

Matt

Hi Matt,

Can you explain what result(s) you want returned?

Looking at Table 2, its layout will make looking up data more of a challenge than it needs to be. Would you be open to changing the layout?

Matty
 

mattbird

Active Member
Joined
Oct 15, 2013
Messages
268
Matty,

The results I would like to be returned is the points in column A (Table 2) and this will depend on gender and age. Columns Q & R (Table 1) is the time the individual completed the run, column P is it converted to decimal minutes. It look for the decimal minutes in the Range floor columns (Table 2) or closest too and then returns the points from column A (Table 2).

Example:

Male: 25 yrs old runs 11min 06sec (shown in Table 1 columns Q&R, which equals 11:10 D.mins column P) and receives 62 points (looks at column B Table 2 equivalent to the time highlighted Yellow in columns D&E and returns the points from column A).

Female: 36 yrs old runs 11:06 and receives 99 points (looks at column V Table 2 equivalent to the time highlighted yellow in columns T&U and returns the points from column A).

How would the layout change?

Matt
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,089
Messages
5,599,667
Members
414,326
Latest member
Aerith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top