Index formula

M

mattbird

Active Member
Joined
Oct 15, 2013
Messages
305
Office Version
  1. 2016
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
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
 
Upvote 0
mattbird said:
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
Click to expand...

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

Matty
 
Upvote 0
Thanks for your help.

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

Matt
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
mattbird said:
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
Click to expand...

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
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

1
  • Question
Index + Match on a table: when sorting table options, I+M breaks
Replies
1
Views
632
Fluff
Fluff
T
  • Solved
Sumproduct data on different sheets and multiple criteria
Replies
3
Views
678
Fluff
Fluff
W
  • Solved
Removing Data Validation List Items Once Selected In Another Cell
Replies
3
Views
398
James006
James006
J
  • Solved
INDEX/AGGREGATE/ROW Formula
Replies
3
Views
782
Fluff
Fluff
V
  • Question
Returning Randomly Selected Value from Table based on Criteria
Replies
4
Views
594
Viau1337
V

Forum statistics

Threads
1,214,908
Messages
6,122,187
Members
449,071
Latest member
cdnMech

Share this page

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
Back
Top