Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.