How do I get excel to show a specific answer for a number falling between two points?

[Emma Duncan]

I am trying to create a spreadsheet for use in Pavement Design. I need a formula that can pick up what answer is required when the user types in a specific number.

For example: If the traffic count is between 100-299 then I want 98 to appear in the answer box, however if the traffic count is between 300-599 then I want 97 to appear. There are loads of these that go up in 200's then 10,000 (approximatley 23 possible answers!) any help would be greatly appreciated!

Em

[Mariostg]

A "quick" and dirty way would be to fill in a sheet with all the possible traffic count and beside it the answer:
100 98
101 98
...
300 97
301 97

etc.
Then use VLOOKUP to get the "answer"

[Emma Duncan]

Cheers, but it's for work and the traffic count data goes up to 100,000, could be a massive sheet!! Is there any other way?

[Mariostg]

Oh I see, then this is not a good option.
You probably want to do that in VBA then and activate the selection through a button. Basic idea is like this:

x = Cells(1, 1)
Select Case x
    Case 100 - 299
        a = 23
    Case 300 - 400
        a = 34
    Case Else
        a = 0
End Select

[OldBirdman]

I would create another worksheet (tab) with these values. ColumnA would be upper limit, Column B the desired answer.

   A      B
   0      0
  99     99
 299     98
 599     97
....
9999     66

I would write a VBA function to check each row, from 2 until ColumnA is blank. When the test value exceeds ColumnA, return the answer in ColumnB, row = Current - 1.

This would make it easy to maintain. You would probably want to re-sort before checking, and also error check the input to function.

[NeoPa]

You want VLookup() Em. If the fourth parameter is set as TRUE then it treats the table as an ordered list and finds the closest match upwards.

The attachment includes a Word document that covers this in detail.

[NeoPa]

For those not wanting to look at the attachment the main contents of this are as below. The attachment does have some examples shown though, so may be worth the effort. Also, the text contains references to the diagrams which are less useful without them of course.

VLookUp(A, B, C, D)

VlookUp or Vertical LookUp is a function that enables Excel to ‘Find’ related items from within a range stored elsewhere. Elsewhere can mean another excel workbook on another PC, but can also mean a range in the current worksheet.
In any Range reference in Excel, a ’$’ preceeding an element of the address, simply tells Excel not to be clever when copying or dragging the formula to other cells. Notice the formulas displayed in column B below: First B1 is created, then that cell is copied and pasted, or dragged down over, the other cells. Notice the only item that changes is the number (Row reference) after $A.

1. This is the item to search for and is often a cell reference. The ‘$’ is usually required here for the column letter but rarely for the Row number.
   
2. This is a reference to the range being searched. This is almost always fully referenced with ‘$’s.
   If it is a requirement to refer to ranges in other worksheets or even other workbooks, the format for each of these is illustrated in cells E1 & E3 below respectively. For space reasons, the range in Book2 starts at B3 rather than at E5, but ‘[Book1]Sheet 2’ has the data in the same place as Sheet1 (E5:F9). Basically, a worksheet reference preceeds the cell part with a ‘!’ separating them. Any complicated name (reserved word or containing unusual characters including spaces) requires singe quotes (‘) around it – Sheet2!$A$1:$C$ 9 but ‘Sheet 2’!$A$1:$C$ 9. A workbook reference preceeds the worksheet reference and is surrounded in [] brackets. If (‘)s required in sheet name then the workbook reference follows the first (‘) – ‘[Book2]Sheet 2’!$A$1:$C$ 9.
   
3. This is the column to display if a match is found. In the example below, the words (Abacus; Broadcast; …) are found in column F, but in column 2 from the left of the range. Column C shows the results displayed when the formula in Column B is used.
   
4. This indicates to Excel how it should treat items where a match isn’t found :
   True (or non-zero) indicates it expects an ordered table and it will return the next item in the list after the point this item would have been. This is never used for simply finding items in a list. This would be used by accountants sometimes.
   False (or zero) will only find exact matches. Any item not found would return a #N/A value (See C4 below) which can be detected by using the ISNA() function.

[Emma Duncan]

I think I might have nearly got it! The formula I'm using is =VLOOKUP(D12,J5 :K27,1)

D12 is the cell with the area of users to input their data.

J5:K27 are the two columns with corresponding answers

and the 1 is the first column that I want excel to look up and give me the answer from the 2nd column.

Could someone tell me whats wrong with the formula, as I keep getting the NA error!


Thanks

Em

[Emma Duncan]

I figured it out! Cheers for all your help

Em

[NeoPa]

Hi Emma. Sorry for the lack of responses (See NeoPa Absence).

Your problem (as far as I can tell without the explanation and any data) is that you have a third parameter (1) which is probably wrong for both of the remaining parameter values. The first two parameters seem fine (but I would strongly recommend using the $ chars to fix the search range - as in VLOOKUP(D12,$J$ 5:$K$27,...)), but after that you need a third parameter (C from post #7) that should probably be 2. I'm assuming here you want to return the value from column K. After that you would need the value TRUE for the fourth parameter (D from post #7) to indicate not to require an exact match.

Good luck with your project :)