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

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]
``````
-
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

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)

-

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.

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