Share via

EXCEL 2010/ Finding the closest value to a given cell, considering two variables

Anonymous
2015-01-29T23:39:16+00:00

I Have 3 destinations and I make several trips in a year to either destination, so:

  1. I want to find the closest date I traveled to a given destination, within its group of dates.
  2. I want to convert this value to a position in the spreadsheet
  3. Finally, I want to take such value, keep the row number and change the column to have a new position in the spreadsheet (for example column F)

So, it should be something like this:

Destination DATE Desired Result 1 Desired result 2 Desired Result 3
A January-01-1999 "FIRST ENTRY" Column B, Row 1 (B1) F1
B February-01-1999 "FIRST ENTRY" B2 F2
C March-01-1999 "FIRST ENTRY" B3 F3
A December-01-1999 October-01-1999 B5 F5
A October-01-1999 January-01-1999 B1 F1
B September-01-1999 February-01-1999 B2 F2

I thought I could use something like {=MAX(IF(Destination=a1&DATE<b1,DATE)}

However, it returns the value of "0"

I am using Excel 2007-2010; I am looking forward to your ideas.

Thank you!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-01-30T19:46:37+00:00

    Have a look at this link: http://www.get-digital-help.com/2009/05/18/finding-the-nearest-date-in-a-range-of-dates-using-excel-formula/

    Thank you, but I need some extra help.

    If I use for example, "Find the closest value with criterion" from that website, the formula returns the closest "equal or lower" value, and I want the lower value, only.

    I am looking forward to your comments,

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-01-30T17:54:14+00:00

    Was this answer helpful?

    0 comments