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
 
Sort by date Sort by votes
Hi Matt,

Based on the example data, it looks like:

Code: 
=INDEX(A5:A6,MATCH(P15,INDEX(B5:BM6,0,MATCH(1,IF(B2:BM2>=E15,IF(B4:BM4=F15,1)),0)-2),1))

Commit with CTRL+SHIFT+ENTER and adjust ranges to suit your real data.

For the formula to work, you need to stipulate the UPPER bounds in Table 2 in each column. For example, B:I would be 29 and J:Q would be 34. The row where this data resides can be hidden if needs be.

Post back if anything is unclear.

Matty
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Matty,

I have tried using the same formula for a slightly different table to return a result but can'y get it to work. This time age is the only factor so I modified the formula slightly.

Code: 
=INDEX('PFA Conv Table - Sit Ups'!$A$6:$A$57,MATCH(PFA!M15,INDEX('PFA Conv Table - Sit Ups'!$B$6:$I$57,0,MATCH(1,IF('PFA Conv Table - Sit Ups'!$B$3:$I$3>=PFA!E15,1),0)-2),1))

The input table (Table 1) is the same as before, the results table (Table 2) is different.

Table 1:

​
Excel 2007
DEFGHIJKLMNO
11DOBAGEM/FSQNDate ofDate of NextPRESS - UPSSIT - UPS
12COYAssessmentAssessmentSCOREPOINTSRESULTSCOREPOINTSRESULT
13
1411/04/9025M 
1511/04/5164M 670F
PFA
Cell Formulas
RangeFormula
E14=IF(D14="","",DATEDIF(D14,NOW(),"Y"))
E15=IF(D15="","",DATEDIF(D15,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)))
O15=IF(M15=0,"",IF(N15>=70,"P","F"))
N15{=INDEX('PFA Conv Table - Sit Ups'!$A$6:$A$57,MATCH(PFA!M15,INDEX('PFA Conv Table - Sit Ups'!$B$6:$I$57,0,MATCH(1,IF('PFA Conv Table - Sit Ups'!$B$3:$I$3>=PFA!E15,1),0)-2),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
PF=List!$E$2:$E$3
PFA=List!#REF!



Table 2 is in the next post
 
Last edited:
Upvote 0
Table 2


Excel 2007
ABCDEFGHIJ
3Upper Bounds2934394449545964
4Age Group
5Points< 3030 - 3435 - 3940 - 4445 - 4950 - 5455 - 5960 - 64Points
61007772716762615753100
799767170666160565299
8987069656059555198
997756968645958545097
1096746867635857534996
PFA Conv Table - Sit Ups


I put the upper bounds in as before. The table points go down to 50 point as before.


I have two more table I need to do the same with a press up and blep test after this one if you don't mine helping

Matt
 
Last edited:
Upvote 0
Hi,

The following should work:

Code: 
=INDEX('PFA Conv Table - Sit Ups'!A$6:A$10,MATCH(M14,INDEX('PFA Conv Table - Sit Ups'!B$6:I$10,0,MATCH(1,IF('PFA Conv Table - Sit Ups'!B$3:I$3>=E14,1),0)),1))

Committed with CTRL+SHIFT+ENTER.

However, your data needs sorting in ascending order for the MATCH function to work, meaning it needs to be like this:

Excel 2012
ABCDEFGHI
3Upper Bounds2934394449545964
4Age Group
5Points< 3030 - 3435 - 3940 - 4445 - 4950 - 5455 - 5960 - 64
6967468676358575349
7977569686459585450
898070696560595551
9997671706661605652
101007772716762615753

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
PFA Conv Table - Sit Ups​
Matty
 
Upvote 0
Hi,

The formula works thanks. I have another where I have used the above formula but its not working and I think it is because of the format I am after. It for a blep test and the result is shown as eg 10'2 which stands for level 10, lap 2.

Formula:
Code: 
= INDEX('PFA Conv Table - MSFT'!$A$7:$A$58,MATCH(U14,INDEX('PFA Conv Table - MSFT'!$B$7:$Q$58,0,MATCH(1,IF('PFA Conv Table - MSFT'!$B$3:$Q$3>=E14,IF('PFA Conv Table - MSFT'!$B$5:$Q$5=F14,1)),0)),1)))

Input table:
Excel 2007
DEFGHIJKLMNOQRSTUVW
11DOBAGEM/FSQNDate ofDate of NextPRESS - UPSSIT - UPSMSFT
12COYAssessment AssessmentSCOREPOINTSRESULTSCOREPOINTSRESULTMINSECPOINTSRESULTSCOREPOINTSRESULT
13
1411/04/9025M00011'1185P
1511/04/5164M000

<colgroup><col style="width: 25pxpx"><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​

Worksheet Formulas
CellFormula
L14=IF(J14="","",IF(K14>=70,"P","F"))
L15=IF(J15="","",IF(K15>=70,"P","F"))
O14=IF(M14="","",IF(N14>=70,"P","F"))
O15=IF(M15="","",IF(N15>=70,"P","F"))
E14=IF(D14="","",DATEDIF(D14,NOW(),"Y"))
E15=IF(D15="","",DATEDIF(D15,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)))
T14=IF(Q14="","",IF(S14>=70,"P","F"))
T15=IF(Q15="","",IF(S15>=70,"P","F"))
W14=IF(U14="","",IF(V14>=70,"P","F"))

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

<tbody>
</tbody>

Array Formulas
CellFormula
K14{=IF(J14="",0,INDEX('PFA Conv Table - Press Ups'!$A$6:$A$57,MATCH(J14,INDEX('PFA Conv Table - Press Ups'!$B$6:$Q$57,0,MATCH(1,IF('PFA Conv Table - Press Ups'!$B$3:$Q$3>=E14,IF('PFA Conv Table - Press Ups'!$B$5:$Q$5=F14,1)),0)),1)))}
K15{=IF(J15="",0,INDEX('PFA Conv Table - Press Ups'!$A$6:$A$57,MATCH(J15,INDEX('PFA Conv Table - Press Ups'!$B$6:$Q$57,0,MATCH(1,IF('PFA Conv Table - Press Ups'!$B$3:$Q$3>=E15,IF('PFA Conv Table - Press Ups'!$B$5:$Q$5=F15,1)),0)),1)))}
N14{=IF(M14="",0,INDEX('PFA Conv Table - Sit Ups'!A$6:A$57,MATCH(M14,INDEX('PFA Conv Table - Sit Ups'!B$6:I$57,0,MATCH(1,IF('PFA Conv Table - Sit Ups'!B$3:I$3>=E14,1),0)),1)))}
N15{=IF(M15="",0,INDEX('PFA Conv Table - Sit Ups'!A$6:A$57,MATCH(M15,INDEX('PFA Conv Table - Sit Ups'!B$6:I$57,0,MATCH(1,IF('PFA Conv Table - Sit Ups'!B$3:I$3>=E15,1),0)),1)))}
S14{=IF(P14=0,0,INDEX('PFA Conv Table - 1.5 Mile Run'!$A$6:$A$57,MATCH(PFA!P14,INDEX('PFA Conv Table - 1.5 Mile Run'!$B$6:$BM$57,0,MATCH(1,IF('PFA Conv Table - 1.5 Mile Run'!$B$3:$BM$3>=PFA!E14,IF('PFA Conv Table - 1.5 Mile Run'!$B$5:$BM$5=PFA!F14,1)),0)-2),1)))}
S15{=IF(P15=0,0,INDEX('PFA Conv Table - 1.5 Mile Run'!$A$6:$A$57,MATCH(PFA!P15,INDEX('PFA Conv Table - 1.5 Mile Run'!$B$6:$BM$57,0,MATCH(1,IF('PFA Conv Table - 1.5 Mile Run'!$B$3:$BM$3>=PFA!E15,IF('PFA Conv Table - 1.5 Mile Run'!$B$5:$BM$5=PFA!F15,1)),0)-2),1)))}
V14{=IF(U14="",0,INDEX('PFA Conv Table - MSFT'!$A$7:$A$58,MATCH(U14,INDEX('PFA Conv Table - MSFT'!$B$7:$Q$58,0,MATCH(1,IF('PFA Conv Table - MSFT'!$B$3:$Q$3>=E14,IF('PFA Conv Table - MSFT'!$B$5:$Q$5=F14,1)),0)),1)))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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

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

<tbody>
</tbody>






The results table in the next post.
 
Last edited:
Upvote 0
Results Table:
Excel 2007
ABCDEFGHIJKLMNOPQ
4Age Group< 3030 - 3435 - 3940 - 4445 - 4950 - 5455 - 5960 - 64
5GenderMFMFMFMFMFMFMFMF
6PointsLevel/LapLevel/LapLevel/LapLevel/LapLevel/LapLevel/LapLevel/LapLevel/LapLevel/LapLevel/LapLevel/LapLevel/LapLevel/LapLevel/LapLevel/LapLevel/Lap
700'0000000000000000
8508'106'58'46'17'95'67'46'95'8
9519'06'66'28'05'77'55'37'04'8
10528'56'38'15'85'45'06'04'0
11539'16'78'66'07'65'57'15'16'14'1
12549'26'88'76'48'27'77'25'26'24'2
13556'98'86'58'36'15'67'36'34'35'8
14569'36'68'46'27'85'75'36'44'46'0
15579'47'08'96'37'95'87'45'46'56'14'0
16587'18'106'78'58'07'56'64'56'24'15'8
17599'57'29'06'88'66'48'16'07'65'56'74'66'34'26'1
18609'67'39'16'98'76'58'26'17'75'66'84'76'44'36'24'0
19619'76'66'25'74'86'54'46'3
20627'49'27'08'88'36'37'85'86'95'06'66'4
21639'87'59'37'18'96'78'47'96'07'06'74'56'5
22649'97'67'28'106'88'56'48'07'15'16'84'66'64'1
23657'79'47'39'06'98'66'58'16'17'25'24'76'74'2
24669'109'57'08'76'68'26'27'35'36'94'86'84'3

<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 Conv Table - MSFT​



I have also tried 11/11 as a format but this doesn't work either. I know it works if I use 11.11 but this will be an issue when using 10.10 (level 10, lap 10) as it will show as 10.1 (level 10, lap 1) if you see what i mean. Any ideas how I can get this to work with either formats???

Your continued help is greatly appreciated

Matt
 
Last edited:
Upvote 0
Hi,

Is there a unique Level/Lap for each Point available per Age Group and Gender? Your data suggests not, but could there be?

This would be the only way I think you could do this.

Matty
 
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
636
Fluff
Fluff
T
  • Solved
Sumproduct data on different sheets and multiple criteria
Replies
3
Views
679
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
785
Fluff
Fluff
V
  • Question
Returning Randomly Selected Value from Table based on Criteria
Replies
4
Views
594
Viau1337
V

Forum statistics

Threads
1,214,982
Messages
6,122,581
Members
449,089
Latest member
Motoracer88

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