Index match question

[SGT Buckeye]

=INDEX(B1:B10,
IF(ISNUMBER(MATCH(C1,A1:A10,0)),MATCH(C1,A1:A10,0),
1+MATCH(C1,A1:A10,1)))

I put my table in A1:B10, the value to lookup in C1.

I found this formula in another post. I entered some data into these
cells to see if it worked and it worked perfectly. The credit goes to
Dave Peterson, not me. I am trying to modify this formula to look up
a value that is dependent on multiple criteria. On the worksheet that
contains the formula, a person's sex is in cell C4, age in cell D4,
and run time in cell I4. It will need to look in a range (cell
A1:U141) called "2MI DATA" on a separate worksheet called 2 Mile Run.
In the following sample of the "2MI DATA" below, "AGE" is in cell a1.

AGE 17-21 17-21 22-26 22-26 27-31 27-31
Time M F M F M F
12:48 100 100 100 100 100 100
12:54 100 100 100 100 100 100
13:00 100 100 100 100 100 100
13:06 99 100 99 100 100 100
13:12 97 100 98 100 100 100
13:18 96 100 97 100 100 100
13:24 94 100 96 100 99 100
13:30 93 100 94 100 98 100

Can the match/index formula above be modified to do the following:
For a 20 year old male who runs 13:26, the formula should return a
value of 93.

Thanks for any help you can provide.

 

[SGT Buckeye]

=INDEX(B1:B10,
IF(ISNUMBER(MATCH(C1,A1:A10,0)),MATCH(C1,A1:A10,0),
1+MATCH(C1,A1:A10,1)))

I put my table in A1:B10, the value to lookup in C1.

I found this formula in another post. I entered some data into these
cells to see if it worked and it worked perfectly. The credit goes to
Dave Peterson, not me. I am trying to modify this formula to look up
a value that is dependent onmultiplecriteria. On the worksheet that
contains the formula, a person's sex is in cell C4, age in cell D4,
and run time in cell I4. It will need to look in a range (cell
A1:U141) called "2MI DATA" on a separate worksheet called 2 Mile Run.
In the following sample of the "2MI DATA" below, "AGE" is in cell a1.

AGE 17-21 17-21 22-26 22-26 27-31 27-31
Time M F M F M F
12:48 100 100 100 100 100 100
12:54 100 100 100 100 100 100
13:00 100 100 100 100 100 100
13:06 99 100 99 100 100 100
13:12 97 100 98 100 100 100
13:18 96 100 97 100 100 100
13:24 94 100 96 100 99 100
13:30 93 100 94 100 98 100

Can thematch/indexformula above be modified to do the following:
For a 20 year old male who runs 13:26, the formula should return a
value of 93.

Thanks for any help you can provide.

Just to clarify, if a person's run time falls between two of the run
times listed in column A, the formula should return the lower value.
I am using this for my Army unit to compile test scores for the Army
physical fitness test. Thanks again for the help.

 

[Earl Kiosterud]

-----------------------------------------------------------------------

Just to clarify, if a person's run time falls between two of the run
times listed in column A, the formula should return the lower value.
I am using this for my Army unit to compile test scores for the Army
physical fitness test. Thanks again for the help.

Sgt Buckeye,

First, I would simplify this by separating your M and F tables. Also, remove the upper age
range value (e.g.: instead of 17-21, put only 17); that is, list only the lower of each. In
sheet "2MI DATA", put the M table, starting in A1, as follows:

Male
17 22 27 32
12:48 100 100 100
12:54 100 100 100
13:00 100 100 100
13:06 99 99 100
13:12 97 98 100
13:18 96 97 100
13:24 94 96 99
13:30 93 94 98


The "Male" in A1 is just a heading, for our edification, and not used by the formulas.
Since there's no score for age 32 and up, i've left that column blank. You could put scores
in it, and any age 32 and above would get that score.

Now, for M only, you would use this formula:
=VLOOKUP(I4,'2MI DATA'!A3:E10,MATCH(D4,'2MI DATA'!B2:E2,1)+1)

It's a VLOOKUP that uses a MATCH to figure out which column to tell the VLOOKUP to use (its
third argument).

I've put the F table in starting in G1 of sheet 2MI DATA:

Female
17 22 27 32
12:48 100 100 100
12:54 100 100 100
13:00 100 100 100
13:06 100 100 100
13:12 100 100 100
13:18 100 100 100
13:24 100 100 100
13:30 100 100 100


Now to send it to the M or F table, wrap the above, and a similar one adjusted for the F
table, in an IF statement:

=IF(C4="M",VLOOKUP(I4,'2MI DATA'!A3:E10,MATCH(D4,'2MI DATA'!B2:E2,1)+1),VLOOKUP(I4,'2MI
DATA'!G3:K10,MATCH(D4,'2MI DATA'!H2:K2,1)+1))

This presumes that if M isn't in C4, it's F -- it doesn't actually look at it. You should
use Data - Validation on that cell allowing only M or F, or an entry other than M or F might
give you incorrect results, like if someone puts "Don't ask, don't tell" in the cell.

Now get an appointment, in advance (cause you're gonna need it), with a psychiatrist, yoga
instructor, or get a big bottle of downers, because this is not a good thing to try to
maintain or debug. It's too messy, and will have you barking within hours if it ever breaks
or has to be changed. I made it by pasting separate formulas together, dealing with smaller
pieces. I'd recommend splitting it up into separate pieces (like the M only formula above),
then hiding the those columns or rows of those cells. It makes maintenance MUCH better.
Concise is for geeks. Verbose is better for maintainability (changing stuff later) and
reliability (less chance of an error in the formulas). Post back if you're interested.

Also, it'd be a bit better (and better design) to put the M and F tables in separate sheets,
like "2MI DATA M", and "2MI DATA F". That way, the tables would be in the same places of
their respective sheets.

This is not thoroughly tested. I've left that to you. But I think it's pretty close to
what you need.

 

[Earl Kiosterud]

-----------------------------------------------------------------------


Sgt Buckeye,

First, I would simplify this by separating your M and F tables. Also, remove the upper
age range value (e.g.: instead of 17-21, put only 17); that is, list only the lower of
each. In sheet "2MI DATA", put the M table, starting in A1, as follows:

A B C D E
1 Male
2 17 22 27 32
3 12:48 100 100 100
4 12:54 100 100 100
5 13:00 100 100 100
6 13:06 99 99 100
7 13:12 97 98 100
8 13:18 96 97 100
9 13:24 94 96 99
10 13:30 93 94 98

The "Male" in A1 is just a heading, for our edification, and not used by the formulas.
Since there's no score for age 32 and up, i've left that column blank. You could put
scores in it, and any age 32 and above would get that score.

Now, for M only, you would use this formula:
=VLOOKUP(I4,'2MI DATA'!A3:E10,MATCH(D4,'2MI DATA'!B2:E2,1)+1)

It's a VLOOKUP that uses a MATCH to figure out which column to tell the VLOOKUP to use
(its third argument).

I've put the F table in starting in G1 of sheet 2MI DATA:

G H I J K
1 Female
2 17 22 27 32
3 12:48 100 100 100
4 12:54 100 100 100
5 13:00 100 100 100
6 13:06 100 100 100
7 13:12 100 100 100
8 13:18 100 100 100
9 13:24 100 100 100
10 13:30 100 100 100

Now to send it to the M or F table, wrap the above, and a similar one adjusted for the F
table, in an IF statement:

=IF(C4="M",VLOOKUP(I4,'2MI DATA'!A3:E10,MATCH(D4,'2MI DATA'!B2:E2,1)+1),VLOOKUP(I4,'2MI
DATA'!G3:K10,MATCH(D4,'2MI DATA'!H2:K2,1)+1))

This presumes that if M isn't in C4, it's F -- it doesn't actually look at it. You should
use Data - Validation on that cell allowing only M or F, or an entry other than M or F
might give you incorrect results, like if someone puts "Don't ask, don't tell" in the
cell.

Now get an appointment, in advance (cause you're gonna need it), with a psychiatrist, yoga
instructor, or get a big bottle of downers, because this is not a good thing to try to
maintain or debug. It's too messy, and will have you barking within hours if it ever
breaks or has to be changed. I made it by pasting separate formulas together, dealing
with smaller pieces. I'd recommend splitting it up into separate pieces (like the M only
formula above), then hiding the those columns or rows of those cells. It makes
maintenance MUCH better. Concise is for geeks. Verbose is better for maintainability
(changing stuff later) and reliability (less chance of an error in the formulas). Post
back if you're interested.

Also, it'd be a bit better (and better design) to put the M and F tables in separate
sheets, like "2MI DATA M", and "2MI DATA F". That way, the tables would be in the same
places of their respective sheets.

This is not thoroughly tested. I've left that to you. But I think it's pretty close to
what you need.

Buckeye,

The stuff I pasted into the post, the tables, got messed up, as I thought it might. It's
the spacing. See my quoted post above, where I've cleaned it up. If your newsreader isn't
using a non-proportional font (like Courier), it's still going to be a mess.

 

[T. Valko]

I too would use separate tables. However, since the requirement is if there
is not an exact time match use the next higher time. That's how the OP came
up with this:

For a 20 year old male who runs 13:26, the formula should return a value of
93.

In order to do this the tables need to be sorted descending. Also, all the
Female values seem to be the same, 100 (dummy data?). If that's the real
data then that really simplifies things! You only need to lookup for Males.

Using named ranges (for brevity) one formula can incorporate both genders.

Too much to explain in writing but here's a sample file. I've used 2 tables
but like I mentioned above, if the Female data is the same we can simplify
this even more.

Sample file

Tables.xls 14kb

http://cjoint.com/?fEihEtiLCw

Biff

 

[SGT Buckeye]

I too would use separate tables. However, since the requirement is if there
is not an exact time match use the next higher time. That's how the OP came
up with this:


In order to do this the tables need to be sorted descending. Also, all the
Female values seem to be the same, 100 (dummy data?). If that's the real
data then that really simplifies things! You only need to lookup for Males.

Using named ranges (for brevity) one formula can incorporate both genders.

Too much to explain in writing but here's a sample file. I've used 2 tables
but like I mentioned above, if the Female data is the same we can simplify
this even more.

Sample file

Tables.xls 14kb

http://cjoint.com/?fEihEtiLCw

Biff









- Show quoted text -

Thank you to everyone who offered suggestions but I was able to figure
it out. I modified the worksheet that contained the run time and
points earned data to include headers in the pattern M17, F17, M18,
F18. I then sorted the information in descending order by run time
(see table below).

Time M17 F17 M18 F18
26:36 0 0 0 0
26:30 0 0 0 0
26:24 0 0 0 0
26:18 0 0 0 0

I then used the following formula using a double lookup:

=IF(ISBLANK($J4)," ",IF($J4>=RUN!A2,0,IF($J4<=RUN!A138,100,OFFSET(RUN!
$A$1,MATCH($J4,RUN!$A$2:$A$140,-1),MATCH($E4,RUN!$B$1:$CO$1,0)))))

I have been working on this off and on for two weeks under various
topic titels. Again, many thanks to everyone who has offered help
trying to figure this one out.

 

[T. Valko]

Thank you to everyone who offered suggestions but I was able to figure
it out. I modified the worksheet that contained the run time and
points earned data to include headers in the pattern M17, F17, M18,
F18. I then sorted the information in descending order by run time
(see table below).

Time M17 F17 M18 F18
26:36 0 0 0 0
26:30 0 0 0 0
26:24 0 0 0 0
26:18 0 0 0 0

I then used the following formula using a double lookup:

=IF(ISBLANK($J4)," ",IF($J4>=RUN!A2,0,IF($J4<=RUN!A138,100,OFFSET(RUN!
$A$1,MATCH($J4,RUN!$A$2:$A$140,-1),MATCH($E4,RUN!$B$1:$CO$1,0)))))

I have been working on this off and on for two weeks under various
topic titels. Again, many thanks to everyone who has offered help
trying to figure this one out.

Glad you got it worked out.

I remember those 2m runs. They were a piece of cake! I always finished 1st
or 2nd.

Biff