Converting a SQL Query To Excel Function

Question

My SQL query is not very complex. LeftOverMonthlyIncome is a variable calculated in the spreadsheet.

SELECT CounterOfferLineAmount 
FROM CounterOfferLine
WHERE MinLeftOverIncomeAmount <= LeftOverMonthlyIncome 
AND MaxLeftOverIncomeAmount >= LeftOverMonthlyIncome

Basically, "Give me the CounterOfferLine where LeftOverMonthlyIncome is between MinLeftOverIncome and MaxLeftOverIncome".

I've defined a table in Excel that has this structure:

MinLeftOverIncomeAmount __MaxLeftOverIncomeAmount __CounterOfferLineAmount<br/>
0__________________________30___________________0
30_________________________35___________________600
35_________________________40___________________700
40_________________________45___________________800
45_________________________50___________________900
50_________________________55___________________1000

If LeftOverMonthlyIncome is 43 then the CounterOfferLine should be 800 (43 >= 40 and 43 <= 45 that equates to 800)

How do I convert this into an Excel function? Is this a VLookup, Lookup, If, AND, Match, Index?

Answer 1

You can search for the nearest value with VLOOKUP (vertical lookup):

=VLOOKUP(B11, B3:D8, 3, TRUE)

Where B11 is the value to look up, B3:D8 the matrix with values, 3 the column to select (CounterOfferLineAmount). The last parameter is the "range lookup" option. When set to TRUE or omitted,

If an exact match is not found, the next largest value that is less than lookup_value is returned.

VLOOKUP requires the list to be sorted.