Excel 2007 : Vlookup range

[BLKHWKPILOT74]

I am in need of help in a vlookup range. The formula that i am trying to figure out is for a Land navigation course. It has points found and a time with that. Then we assign a score based on that. What I am trying to do is input the students time and points found and excel gives me the score. The grading scale looks like this

points found / score
time(hrs:min)
3/4 <3:00 1
3/4 <2:30 2
3/4 <2:00 3
3/4 <1:30 4
3/4 <1:00 5
4/4 <3:00 6
and so on

if someone has some help on how to solve this that would be great! Thanks in advance

[rwgrietveld]

I think this is a simple one but your explination is a bit vague.

Based on the Navigation (GPS) points found within a certain time a score is assigned.

So
3 out of 4 points found within 3:00 (h:mm) -> 1 point
3 out of 4 points found within 2:30 (h:mm) -> 2 points
Etc.

Am I right?

[rwgrietveld]

Can't explain my solution properly without an example.

The solution is =LOOKUP(2^16,IF(--(A2:A7=A9)*--(B2:B7>B9),C2:C7))

(array function)

[BLKHWKPILOT74]

Yes you described it right and your solution seems to work. If you find 3 out of 4 points in under 3 hours you get 1 point. I will put it into the spread sheet tomorrow and see how it works. Thanks for the help, I wish I was smart on excel because it is a great tool.

[sharad]

Is this is what u are looking for ... ?

[BLKHWKPILOT74]

rwgrietveld,

I put the formula in my sheet but I keep getting a #VALUE! in that cell. I have changed the values to fit the sheet I am working on but couldnt firgure it out. I was wonderin what 2^16 means in the formula. I know it is probably an easy answer but i am a noob when it comes to excel. I can fly a multi million dollar helicopter but cant figure out excel. I attached the excel sheet that i am working on if you want to look at it. Thanks again.

Steve

[NBVC]

Is the time you put in U24 going to match exactly to U3:U13, always? What is the logic on what you want returned?

[BLKHWKPILOT74]

No the time will not always match the time in U3:U13. For instance if a person goes out and finds 3 out of 4 points in 2 hrs and 13 min then they will recieve 2 points, if they find them in less than 2 hours they recieve 3 points, etc. I am looking for a way so all I have to do is input the points they found and their time and excel returns a score based off of my original post, 3 out of 4 points found in less than 3 hours is 1 point, 3 out of 4 points in less than 2 hrs 30 min is 2 points and so on.

[NBVC]

Try:

=LOOKUP(2,1/((T3:T13=T24)*(U3:U13=ROUNDUP(U24*48,0)/48)),V3:V13)

[BLKHWKPILOT74]

Thank you NBVC seems to work like a charm. Just have one question. Once I have the formula in the cell it will return #N/A if nothing is in the cells that it is looking for. Is there a way to keep the cell blank until I put the points and time in? I have other cells that have vlookup in them but they just have a 0 until I make an input to the cell to its left. Thanks again just about have this solved.

[NBVC]

Try:

=IF(OR(T24="",U24=""),"",LOOKUP(2,1/((T3:T13=T24)*(U3:U13=ROUNDUP(U24*48,0)/48)),V3:V13))