"Earl Kiosterud" wrote in message
...

-----------------------------------------------------------------------
"SGT Buckeye" wrote in message
oups.com...
On May 29, 10:27 pm, SGT Buckeye wrote:
=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.


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.
--
Earl Kiosterud
www.smokeylake.com



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.

--
Earl Kiosterud
www.smokeylake.com