# Index formula

#### mattbird

##### Active Member
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

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Oeldere

##### Well-known Member
use a vlookup table for this kind of job to determine the years.

#### mattbird

##### Active Member
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
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

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

Matt

#### mattbird

##### Active Member

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

</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)

</tbody>

<tbody>
</tbody>

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

</tbody>

<tbody>
</tbody>

Table 2 in next post

Matt

Last edited:

#### mattbird

##### Active Member
Table 2

Excel 2007
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

</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)
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)

</tbody>

<tbody>
</tbody>

The table points go down to 50 anything after is zero points

Last edited:

#### Matty

##### Well-known Member
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
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:

Replies
3
Views
203
Replies
9
Views
308
Replies
12
Views
589
Replies
3
Views
394
Replies
2
Views
222 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,711
Messages
5,833,254
Members
430,200
Latest member ### 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.

### Which adblocker are you using?    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

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