Hello,

Suppose the 12 individuals, whose names are shown in the following figure (Fig. 1), have each served as the President of a certain organization.

Suppose also that each president served for a term of one calendar year and the dates in column C indicate the date on which each respective person began their term.

Next, suppose that column F in Fig. 2 shows the dates of some arbitrary event involving a single individual whose name is shown in column E.

What formula in column G would determine whether the event for a particular person, occurred**Before** or **After** the first day of that person's term as President?

For example, the event for Bill occurred on 24-Jun-2006 (Fig. 3) which was**After** 01-Jan-2001, the beginning of his term. In contrast, Herb's event on 07-Sep-2004 took place **Before** the first day of his term on 01-Jan-2009.

Another example, shown in Fig. 4, indicates that John had two events. The first, on 02-Apr-2002, took place**Before** his first day as President (01-Jan-2005), while the second, on 17-Nov-2006, took place **After**.

So, as stated above, my question is:

What formula in column G would give these**Before's** and **After's** automatically? I think there is a =VLOOKUP() in there somewhere but I am not sure how to put it together.

Thanks

Suppose the 12 individuals, whose names are shown in the following figure (Fig. 1), have each served as the President of a certain organization.

Suppose also that each president served for a term of one calendar year and the dates in column C indicate the date on which each respective person began their term.

Next, suppose that column F in Fig. 2 shows the dates of some arbitrary event involving a single individual whose name is shown in column E.

What formula in column G would determine whether the event for a particular person, occurred

For example, the event for Bill occurred on 24-Jun-2006 (Fig. 3) which was

Another example, shown in Fig. 4, indicates that John had two events. The first, on 02-Apr-2002, took place

So, as stated above, my question is:

What formula in column G would give these

Thanks

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

=IF(F4<VLOOKUP(E4,B$4:C$15

...if the name doesn't exist in the list you'll get #N/A error so you can add IFERROR and use this version to prevent that

=IFERROR(IF(F4<VLOOKUP(E4,

regards, barry

>>>Reading the description.....

Yes this is correct. Except that I am not comfortable with the word "approximate" used in the help file. The value result of the search might be closer to the next higher value but it will return the next lower value if exact not found.

If omitted then the default value is "true".

with VLOOKUP you can use zero interchangeably with FALSE so these two are equivalent

=VLOOKUP(E4,B$4:C$15,2,FAL

and

=VLOOKUP(E4,B$4:C$15,2,0)

....although the FALSE version is technically the correct one.

or you can use 1 interchangeably with TRUE (and that's the same as omitting the 4th argument altogether) so these are all equivalent

=VLOOKUP(E4,B$4:C$15,2,TRU

and

=VLOOKUP(E4,B$4:C$15,2,1)

and

=VLOOKUP(E4,B$4:C$15,2)

Note that if you leave out the 4th argument but not the comma, i.e.

=VLOOKUP(E4,B$4:C$15,2,)

then that is equivalent to using FALSE or zero as the 4th argument.

=VLOOKUP(2.9,A2:B4,2,TRUE)

where A2 is 1, A3 is 2 and A3 is 3 then, as Saqib says, the match will be with the greatest number that's less than or equal to the lookup value, so that would be 2 in my example and the formula would return the value from B3.

If the lookup range isn't sorted ascending then results will not necessarily be as expected.

In many cases where you use VLOOKUP with TRUE you could also use LOOKUP to do the same, these are equivalent too:

=VLOOKUP(2.9,A2:B4,2,TRUE)

and

=LOOKUP(2.9,A2:B4)

LOOKUP always needs a sorted lookup range, and there's no "column index" because values are always returned from the last column [although if the lookup array specified is wider than it is long then it works like HLOOKUP]

regards, barry

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial