Help with looking up values between columns with set numbers

[insomnai]

Hi all, thanks for taking a look at this for me. I have a workbook with 3 columns like below:

Score from | Score To | League Score
10 | 20 | 100
21 | 39 | 200
40 | 59 | 300
60 | 79 | 400

Score: 37 (I put this in manually)

Game Score: *

*This is where I would like the sheet to look at the Game Score and check where the value lies between Score From and Score To, and then return the value of the appropriate line, in this case it would be 200.

My problem is that I can do it one way, with VLOOKUP to check one column for a matching value, but I'm lost when it comes to checking a non-matching value that lands between two columns to return the value...

Any assistance would be greatfully received...

[Richard Buttrey]

Hi,

Assuming E1 is where you enter the score then

=INDEX(C:C,MATCH(E1,A:A))

The assumption is that the table is sorted on column A

[insomnai]

Hi, thank you for responding, but that is not what i'm looking for...

What I'm after is the when I manually add the score to cell D1, what I want the Gamescore cell (E1) to do is take a look at the value in D1, and find the row where the score (D1) is between using columns marked 'From' (A) and 'To' (B), returning the value from the 'C' column into D1.

Is this possible?

Thank you

[Richard Buttrey]

The formula I gave you will do exactly what you want. Have you tried it?

Since you hadn't given any specific cell references or said where you manually enter the score (your example 37), I had to assume a cell. I therefore used E1 as that cell. I' wasn't to know that's where you wanted the formula to go.

So put the formula in E1 and enter your value in D1. All that needs to be changed is the reference to E1 in the formula. So:

=INDEX(C:C,MATCH(D1,A:A))

[Chris 53]

TRY VLOOKUP(D1,A2:C5,3,1)

The ,1 at the end is what tells it to find the next lowest value that you want looking up.

[insomnai]

aaaah Richard, I must apologise, I cannot for the life in me see how the formula works, but it does! I know that INDEX and MATCH looks for a specific occurence of a specified number in a set column and reports back the entry in a column somewhere else, but being able to find a value between those columns when they're not even referred to...

Many thanks Richard for taking more time to look at this.

[Richard Buttrey]

Hi,

In fairness Chris's Vlookup() formula will do just as well. Better in some respects since it only involves one function a VLOOKUP rather than a MATCH and INDEX.
Arguably in processing time that may be microseconds quicker, although I have a suspicion that behind the scenes in machine code a VLOOKUP is probably processing both a MATCH and LOOKUP anyway.