Integrating number in between range with a vlookup

Question

I am working with a data table that contains two columns ( Max Room Height, # of bulbs). Intent is to create a formula which provides the output on # of bulbs to use after the user inputs room height. Here’s the trick, room height number that user inputs can be a random number, and could lie between two max room heights. For example , room height data is available as 10 feet, 12 feet, 14 feet, 16 feet and the and user inputs room height as 15 feet, formula should be able to pick up the # of bulbs corresponding to 16 feet height.

Answer 1

You can try using some of the built-in Excel functions to determine the number of bulbs to use based on the room height. Here's an example using INDEX(...), MATCH(...), and MIN(...):

I do not have access to Excel at the moment, but this worked in LibreOffice Calc v5.1.6.2. Excel seems to have comparable functions.

Long Winded Explanation

Read on, in case a picture is not worth a thousand words!

Table/Data Configuration

1. Create a table with two columns (Columns E and F in the example image).
   • The first column (Column E) represents the upper height boundaries in descending order. Note the upper bound of 10000.
   • The second column (Column F) represents the number of bulbs to use if a given room height falls within the height boundary.
   • So for this example, values between 10000 (inclusive) and 16 (exclusive) should state to forget light bulbs and just use the sun. Values between 16 (inclusive) and 14 (exclusive) should state to use 4 bulbs. Values between 14 (inclusive) and 12 (exclusive) should state to use 3 bulbs... etc.
2. Create a second table that has two columns for the data and results (Columns A and B in the example below; Column C is for illustrative purposes only).
   • The first column (Column A) will contain the "user input" (i.e. variable Room Heights whose number of bulbs should be looked up).
   • The second column (Column B) will contain the formula to calculate the number of bulbs to use based on the "user input" and the height boundaries defined in the first table that we created.

Formula/Calculation

Let's break down the formula that will go into the Column B cells. I left the text for the formula in Column C. You'll notice that only the first parameter of the MIN(...) function changes for each row. The rest of the formula is the same for each row.

Using row 2 as an example, we make use of 3 functions, nested together:

• MIN(A2,E2) - We want to make sure the room height falls within our handled range. This works in conjunction with the the arbitrary upper bound of 10000 that was added to Column E. If we didn't force the data to fit within the upper boundary, we'd probably see some kind of error if the user exceeded the largest value specified in Column E.
• MATCH(MIN(A2,E2),E2:E6,-1) - Essentially, this function finds the height range boundary under which the user entered data falls. This function has three parameters. The first is the user entered data (or the arbitrary upper bound)... MIN(A2,E2) for this row. The second is the height range boundaries (in descending order)... E2:E6. The third is the matching type... -1. The matching type of -1 means "search a descending list of values and stop when your given value (i.e. the first parameter) is equal to or less than a value in the descending list". If the first item in the descending list meets the criteria, the MATCH(...) function returns an index of 1. If the second item in the descending list meets the criteria, the function returns an index of 2... etc.
• INDEX(F2:F6,MATCH(MIN(A2,E2),E2:E6,-1)) - This function essentially looks up our "answer" for the user's input. We found the "index" or "list position" of the height range for the user's input using the MATCH(...) function and we created our table such that the bulb-count for each height range is in the same row (i.e. it has the same "index" or "list position"). The INDEX(...) function accepts two parameters. The first is the range of cells containing the "answers"... F2:F6. The second parameter is the index or list position from the answer cell range that we want to return (i.e. the results of our MATCH(...) function). So if our MATCH(...) function call returns "1", the first cell from the F2:F6 range will be returned (i.e. F2 - Use the Sun!). If our MATCH(...) function call returns "2", the second cell from the F2:F6 range will be returned (i.e. F3 - 4 bulbs)... etc.

There might be better solutions out there depending on the version of Excel you are using. According to the Office documentation as of this writing, the functions used here should be valid for Excel 2007 through 2016.