Excel =LOOKUP() function with two variables

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.  
 Fig. 1
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.
 Fig.2
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.
 Fig. 3
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.
 Fig. 4
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
Steve_BradyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
Hello Steve, in G4 try this formula copied down

=IF(F4<VLOOKUP(E4,B$4:C$15,2,0),"Before","After")

...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,B$4:C$15,2,0),"Before","After"),"Name not in list")

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve_BradyAuthor Commented:
Thanks Barry.  That works great!

I've got a question about the last argument (range_lookup) in VLOOKUP.  (I'm including the syntax and description below,only for my own future reference.)

Reading the description seems to indicate that the main purpose of range_lookup is to specify whether close-approximation matches  are allowed or whether the match must be exact.  Is that correct?

Also, it talks about the range_lookup being either TRUE or FALSE or omitted.  I understand how an argument can be omitted but I don't understand how it can be TRUE or FALSE.  Those terms usually apply to the result. Furthermore, I don't understand the "0" value included in your solution.

Thanks again.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Syntax:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Description:
range_lookup  Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

Important  If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

For more information, see Sort data in a range or table.

If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted.

If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.
0
Saqib Husain, SyedEngineerCommented:
NO POINTS FOR THIS

>>>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".

0
barry houdiniCommented:
Hello Steve,

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

=VLOOKUP(E4,B$4:C$15,2,FALSE)
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,TRUE)
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.

The TRUE version can only be used when the lookup range is sorted ascending. The lookup range is the first column of the table array, In your example that would be B4:B15 which isn't sorted ascending. It's normally used with numbers (but it could be text) so if you used this formula

=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
0
Rory ArchibaldCommented:
(Not for points)

Just one addition:
LOOKUP also has the syntax:
=LOOKUP(2.9,A2:A4,B2:B4)

so you can specify which column you want the value back from that way. (you can of course specify a column to the left of the lookup column too.)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.