How to search specific value based on conditions using VLOOKUP in Excel?

Question

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]

Answer 1

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)

Answer 2

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.