Excel formula looking for value between 2
Hi,
I have a document and I want to use an IF function and don't know how.
Here's how it goes
I have on a sheet "table" those column
A B C
0 5 17
6 10 43
11 15 33
16 20 2
Then on the other sheet "info" I have
A
3
7
11
19
I want a formula that says on sheet "info" look at cell A1 and return the "table" C number where A1 on "info" is comprise between number of A1 A2 on the "table" sheet.
So in my example
A1 = 3, 3 is between 0 and 5 so return 17
A2 = 7 and that's between 6-10 so return 43.
A3 = 11 and that's between 11 and 15 so return 33
A4 - 19 and that's between 16-20 so return 2.
I hope that this is clear enough ;-)
tx!
I have a document and I want to use an IF function and don't know how.
Here's how it goes
I have on a sheet "table" those column
A B C
0 5 17
6 10 43
11 15 33
16 20 2
Then on the other sheet "info" I have
A
3
7
11
19
I want a formula that says on sheet "info" look at cell A1 and return the "table" C number where A1 on "info" is comprise between number of A1 A2 on the "table" sheet.
So in my example
A1 = 3, 3 is between 0 and 5 so return 17
A2 = 7 and that's between 6-10 so return 43.
A3 = 11 and that's between 11 and 15 so return 33
A4 - 19 and that's between 16-20 so return 2.
I hope that this is clear enough ;-)
tx!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If vlookup is better, i'll go for it: what's that formula?
tx!
tx!
I gave you the VLOOKUP formula and a sample workbook in my first post.
ASKER
Hi byndt, it works good.
But there is something I don't understand and it's ... why?
I am used to a vlookup that says "if you find this value in this table please report the value contained in column x of the same table"
How does the formula now figures out I am looking for a number not textually identify but that is located between colunm a and b which has numbers textually identified in them
"here's a value, locate where it is located between a range of numbers in colunm A and B and then report..."
It's the "between 2 numbers" I don't see how excel finds it. And why does it assume it's between numbers in column A and B and not say B and D or E or F (in my original tables the value to return is located in column G and).
tx for the clarification
But there is something I don't understand and it's ... why?
I am used to a vlookup that says "if you find this value in this table please report the value contained in column x of the same table"
How does the formula now figures out I am looking for a number not textually identify but that is located between colunm a and b which has numbers textually identified in them
"here's a value, locate where it is located between a range of numbers in colunm A and B and then report..."
It's the "between 2 numbers" I don't see how excel finds it. And why does it assume it's between numbers in column A and B and not say B and D or E or F (in my original tables the value to return is located in column G and).
tx for the clarification
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect explanation, you're a good teacher, thanks for taking the time!
=INDEX(table!$C$1:$C$4,MAX
To array-enter a formula, click in the formula bar, hold the Control and Shift keys down, then hit Enter. Release all three keys. Excel should respond by adding curly braces { } surrounding your formula.