Attempting a reverse LOOKUP?

[nunya]

I have a set up for individuals physical fitness test results, each test results in the person recieving a certain amount of points. Each PFT has it's own sheet within the workbook, i.e. '2 Mile Run'

'2 Mile Run' lists TIME in column A then in Columns C-E has the associated points for that time, with each column being based on the individuals age in a given range.

Now I've used LOOKUP to find the points for any given individual just fine. The problem I am having is that there is a minimum point structure to pass, i.e. 65. What I need to do is to be able to find "65" with the correct column, based on age, and reference back to column A for the TIME they need to pass. This is to provide their TIME Deficiency, or how much time they need to make up.

This is an example of what I used to get the points they earned, which works:
LOOKUP(E8,'2 Mile Run'!A4:A66,IF(D8<=31,(INDEX('2 Mile Run'!C4:C66,,)),IF(D8<=36,(INDEX('2 Mile Run'!D4:D66,,)),IF(D8<=100,(INDEX('2 Mile Run'!E4:E66,,))))))

So I need to do a reverse lookup, in a sense, and i just can't find any way of getting it work.

Thanks.

[NBVC]

Try:

=LOOKUP(F8,IF(D8<=31,'2 Mile Run'!C4:C66,IF(D8<=36,'2 Mile Run'!D4:D66,'2 Mile Run'!E4:E66)),'2 Mile Run'!A4:A66)

Where F8 houses the points and D8 house the age group (column headers)

[nunya]

FYI....I just tried the INDEX/MATCH to give me a time and it just gives me :00 as a result which is not correct:

=INDEX('2 Mile Run'!$A$4:$A$66,MATCH(65,'2 Mile Run'!$D$4:$D$66,FALSE),1)

mind you, this was just a test to see if it would return the proper time based me telling it which points column to reference, eventually, I want it to pick the proper column based on age without me telling it what column to reference.

[nunya]

NBVC.....I'll give it shot.

edited:

actually that won't work...I need F8 to be 65. 65 being the qualifying points, but putting 65 into that doesn't work, that's what i have been fighting with. The function needs to seach the array for 65 based on the age group column.

lemme see if I can upload the file.

[daddylonglegs]

To find 65 in the relevant column and then the corresponding value in column A possibly

=INDEX('2 Mile Run'!A4:A66,MATCH(65,INDEX('2 Mile Run'!C4:E66,0,MATCH(D8,{0,32,37},0)),0))

Note: I'm assuming that the actual value 65 will alwaya appear, or are you looking for the first value above 65?

[daddylonglegs]

Note: you can only use LOOKUP if columns C to E are sorted ascending. Even then you would only normally want to do so if you wanted to find an approximate match. If you know that 65 will occur then INDEX/MATCH would be a better option

[nunya]

Right now I'm just working on the '2 Mile Run' issue.....

[nunya]

To find 65 in the relevant column and then the corresponding value in column A possibly

=INDEX('2 Mile Run'!A4:A66,MATCH(65,INDEX('2 Mile Run'!C4:E66,0,MATCH(D8,{0,32,37},0)),0))

Note: I'm assuming that the actual value 65 will alwaya appear, or are you looking for the first value above 65?

...gives me an #N/A.

[daddylonglegs]

Yeah, small error, try this with cell formatted as hh:mm, should give you 17:42

=INDEX('2 Mile Run'!A4:A66,MATCH(65,INDEX('2 Mile Run'!C4:E66,0,MATCH(D8,{0,32,37})),0))

[NBVC]

This formula in A26 returned 17:42 which seems right:

=INDEX('2 Mile Run'!A4:A66,MATCH(65,IF(D8<=31,'2 Mile Run'!C4:C66,IF(D8<=36,'2 Mile Run'!D4:D66,'2 Mile Run'!E4:E66)),0))

[nunya]

Holy cr@$, it worked.........

the only thing I need to do is go back and reformat all the times as hh:mm:ss, because if it takes me 17 hours and 42 minutes to run 2 miles, I'm in really really bad shape.

Thanks for such a quick response. I'll work with it some more.

Both of those functions worked, thanks again!

[nunya]

if i copy/paste this function into subsequent lower cells, the array references are not properly aligned.......will placing '$', i.e. '2 Mile Run'!$A$4:$A$66 lock it in?

[NBVC]

if i copy/paste this function into subsequent lower cells, the array references are not properly aligned.......will placing '$', i.e. '2 Mile Run'!$A$4:$A$66 lock it in?

Yes, the $ will "freeze" the ranges when copying/pasting or dragging the formula.

[teylyn]

BenSteve,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Also, please refrain from posting comments into year old threads if you don't have anything to contribute.

[arlu1201]

Kareem22,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.