Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to pass to function VLOOKUP the nearest value to cell D242 as lookup_value. For example I have date 30.08.12 as lookup_value. If there is no value 30.08.12 then search for nearest value to 30.08.12 which is less than 30.08.12. In this case it would be 29.08.12.

    =VLOOKUP(D242;Table_crs.accdb[[Date]:[Sell]];3;FALSE)
Where 
    1. D242 is lookup_value
    2. Table_crs.accdb[[Date]:[Sell]] is table_array
    3. 3 is col_index-_num
    4. FALSE is [range_lookup]
share|improve this question
    
Please add your answer as an answer (when you are able), and also accept it if you are happy with it. – Paul Apr 29 '12 at 12:06
up vote 1 down vote accepted

You don't really need to use LOOKUP and VLOOKUP, try either modifying your original formula by using TRUE as range_lookup, which will make it behave as you want, i.e.

=VLOOKUP(D242;Table_crs.accdb[[Date]:[Sell]];3;TRUE)

.....or use LOOKUP like this

=LOOKUP(D242;Table_crs.accdb[[Date]:[Sell]])

Both of those formulas requires Date column to be sorted ascending (as does your suggestion)

share|improve this answer

To use LOOKUP function as lookup_value argument for outer function VLOOKUP:

=VLOOKUP(LOOKUP(D243;Table_crs.accdb[Date]);Table_crs.accdb[[Date]:[Sell]];3;FALSE)

LOOKUP is used because

If LOOKUP cannot find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

share|improve this answer
    
Please move the whole of your answer to here from the question. – Paul Apr 29 '12 at 12:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .