Linking two VLOOKUP functions - Excel 2003

[watermt]

Worksheet 1 has one VLOOKUP function that provides an employee ID number
after it has been placed in a cell (via the drop-down list selection of the
last name) which comes from an external data source via an SQL query.

Worksheet 2 contains other employee related data (personnel reviews) that I
want to be able to pull into Worksheet 1 by using this employee ID number to
reference (VLOOKUP?) the same Employee ID number from Worksheet 2 which is
also provided by another external data source and also via an SQL query.

I'm trying to compare the two sets of external data (referenced by the
employee ID number - common to both external data sets) and display this on
Worksheet 1.

In a nutshell I want to use the employee ID number on Worksheet 1 to look at
Worksheet 2, search for a match of employee ID number and to place the most
current personnel review date for that staff member.

I believe using VLOOKUP functions should do this but I'm not sure how to tie
this all together. I appreciate any help that may be offered. If further
information is needed please advise.

Mike

 

[T. Valko]

search for a match of employee ID number and to

place the most current personnel review date for
that staff member.

So you want the most recent date that matches the employee ID. If the data
is sorted or grouped together so that the most recent date for an ID is the
*first* entry for that ID then you could use a basic VLOOKUP since that will
*always* find the first instance of the ID.

If the data is not sorted in that manner then you can use an array** MAX IF
formula:

A1 = lookup ID

=MAX(IF(ID_range=A1,Date_range))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Format as Date

 

[T. Valko]

search for a match of employee ID number and to

place the most current personnel review date for
that staff member.

So you want the most recent date that matches the employee ID. If the data
is sorted or grouped together so that the most recent date for an ID is the
*first* entry for that ID then you could use a basic VLOOKUP since that will
*always* find the first instance of the ID.

If the data is not sorted in that manner then you can use an array** MAX IF
formula:

A1 = lookup ID

=MAX(IF(ID_range=A1,Date_range))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Format as Date