Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel formula looking for value between 2

Posted on 2013-11-06
7
Medium Priority
?
591 Views
Last Modified: 2013-11-07
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!
0
Comment
Question by:philjans
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 81

Assisted Solution

by:byundt
byundt earned 2000 total points
ID: 39628674
Consider a VLOOKUP formula like:
=VLOOKUP(A1,table!A$1:C$4,3)

Note that the data on worksheet table will need to be sorted in ascending order by column A.
VLOOKUP-Q28287281.xlsx
0
 
LVL 81

Expert Comment

by:byundt
ID: 39628981
If you insist on using an IF function in the formula, the following array-entered formula will work. I much prefer the VLOOKUP, however.
=INDEX(table!$C$1:$C$4,MAX(IF(A1>=table!$A$1:$A$4,ROW(table!$A$1:$A$4),"")))

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.
0
 

Author Comment

by:philjans
ID: 39630238
If vlookup is better, i'll go for it: what's that formula?
tx!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 81

Expert Comment

by:byundt
ID: 39630254
I gave you the VLOOKUP formula and a sample workbook in my first post.
0
 

Author Comment

by:philjans
ID: 39630370
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
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 39630696
There are two forms of VLOOKUP.

The one you are used to is called the "four parameter" or "exact" form of VLOOKUP. It searches the first column of a lookup table for an exact match to the first parameter, then returns a value from the same row. It performs this search row by row, and does not require the lookup table to be sorted by the first column.
=VLOOKUP(A2, lookupTable, 3, FALSE)        find A2 in first column of lookupTable, then return value from third column

The other one is called the "three parameter" or "approximate" form of VLOOKUP. It also searches the first column of a lookup table, but looks for the largest value equal or less than the first parameter. It then returns a value from the same row. This form of VLOOKUP requires that the lookup table be sorted by its first column, and it performs a binary search. The three parameter form of VLOOKUP has two principal uses: bracket pricing and really fast lookups when you know that the first parameter will always be in the lookup table.

Your problem is like bracket pricing, and so is a good candidate for the three parameter form of VLOOKUP. In bracket pricing, the first column of the lookup table should contain the bottom (lowest number) in each bracket. The top (highest number) in each bracket is assumed to be just a smidge less than the value in the next row of the first column. So the VLOOKUP is not looking directly for a value between a range of numbers in columns A and B of the lookup table, but it accomplishes the same end. Both of the following formulas apply to a bracket pricing type of problem
=VLOOKUP(A1,table!A$1:C$4,3)              If fourth parameter is omitted, it is assumed to be TRUE
=VLOOKUP(A1,table!A$1:C$4,3,TRUE)


Just to complete the picture, binary search means to repeat a process of testing the middle of a range to see if the value is higher or lower than the one being sought. Suppose you have a million rows in your lookup table. Rather than searching each one, you first check the value in row 500,000. If it is larger than the value you want, your answer must be in the first 500,000 rows. If it is smaller than the value you want, your answer must be in the last 500,000 rows. You then repeat the process on the smaller range found as a result of the first test. With 21 tests, you will find the desired match--an impressive improvement over searching a million rows one at a time.

The problem with binary search when you need an exact match is if you are looking for a value that falls between two values in column A. Instead of giving you an error that you can trap with ISNA or IFERROR, it gives you the result from the row at the bottom of that bracket.
0
 

Author Closing Comment

by:philjans
ID: 39630866
Perfect explanation, you're a good teacher, thanks for taking the time!
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question