Hi, I need help on excel WHat i really need is to get a Date on a list with a reference date. Below is my example.

A- B - C

April 19 - March 20 - ?

- April 30

- May 16On the above example, the reference is the April 19, now on column C I need to have a result of April 30.

This should not be hard coded.Please help me with this.

Thanks,

Red

I think we need some more detail. What is the relationship between the reference date and the desired date?

There has to be something for Excel to calculate or lookup, etc. The desired result can't be something random that you just "want".

The list on column C is the deadline of the exact day of payment. While the April 19 which is the reference date is the completion date of all collection. Since the reference date and the exact day of payment cannot be move, we need to know when is the exact day of payment.

In short we need to search in column C the nearest succeeding date referencing to April 19.

I hope this one gives you a better idea of what we need.

Thanks,

Red

I'm lost. Please provide examples of input and outpur dates and we'll see what we can do.

Im sorry if I'm making you confused. Here's an example

My input is April 30.

Now I have to selct on the list of available date. Example list are April 29, May 2, May 23, August 5 and August 24

The condition is I need to get the nearest succeeding date. In this example the answer is May 2.I hope this one would help.

Thank you very much.

-Red

If you can sort your list of dates in descending order, which is required by the MATCH function if you want to find a value that is greater than the lookup_value, this should work: Assume your descending list of dates is in A1:A4 and the date you want to look up is in C1.

=INDEX(A1:A4,MATCH(C1,A1:A4,-1),1)

If you can't sort your list of dates in descending order, you could create a descending list in another location using the LARGE function and then modify the formula above to search that list. (You could even hide this list if you want)

Let's say your dates in A1:A4 are in random or ascending order and the date you want to look up is in C1. In F1, put this formula and drag it down to F4:

=LARGE($A$1:$A$4,ROW())

This should create a descending list of your dates in F1:F4 which will get updated if you change the list in A1:A4. The ROW() function generates the k argument for the LARGE function. There are certainly many other ways to generate the k argument, including just typing it.

Now you can use this list by modifying my other formula:

=INDEX($F$1:$F$4,MATCH(C1,$F$1:$F$4,-1),1)

The only other option I can think of is a UDF written in VBA.

Hope that helps!

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History