IFs with VLOOKUPs

[Brad Autry]

Greetings. My scenario is as follows:

I have a look-up table with four fields:

position code, begin date, end date, and job code.

The job codes assigned to a position can change over time, but don't
necessarily. For example:

position begin date end date job code
1234 1/1/2009 4/1/2009 5678
1234 4/2/2009 12/31/9999 9012
4673 1/1/1900 12/31/9999 4736

On another table I have a list of people, a corresponding position code,
along with a date.

I need to assign the job code based on whether the date falls into the range
of begin and end date on the lookup table.

I tried the following with mixed results:

=IF(AND(VLOOKUP(I2,joblookup,2,0)<=G2,VLOOKUP(I2,joblookup,3,0)>=G2),VLOOKUP(I2,joblookup,4,0),"f'd up")

where: I2 is the position code on the table with employees

joblookup is a named range for the entire position/beg date/end date/job
code table (column 2 is the begin date, 3 end date, 4 job code)

G2 is the date associated with the employee

I receive accurate results in about 75% of the cells. The other 25% I'm
getting "F'd up" as a result and I can't determine the common factor amongst
these cells to figure out why. I'm assuming there's a problem because there
are multiple rows on the lookup table with the same position code and perhaps
vlookup requires unique values in the column. I'm not certain what else to
try, though.

Thank you for wading through this lengthy post.

Any ideas or suggestions would be greatly appreciated.

Regards,
Brad

 

[vezerid]

Assuming joblookup in A2101.

=INDEX($D$2:$D$101,MATCH(1,($A$2:$A$101=I2)*($B$2:$B$101>=G2)*($C$2:$C
$101<=G2),0))

This is an *array* formula (commit with Shift+Ctrl+Enter). It will
find the first job code in this position that falls between the two
dates.

HTH
Kostis Vezerides

 

[Pete_UK]

Yes, where you have duplicate codes VLOOKUP will only find the first
one. The solution is to ensure that the position codes are not
duplicated, but how you achieve that will depend on your procedures.

Hope this helps.

Pete

 

[Teethless mama]

=SUMPRODUCT((position=I2)*(begin_date<=G2)*(end_date>=G2),job_code)

 

[Brad Autry]

Thanks to everyone who offered advice/suggestions. I had some issues with
the array formula, so I spent the morning reviewing that INDEX & MATCH
procedure before something else came up I had to attend to. I tried the
SUMPRODUCT route when I got back to this project; that one worked like a
charm, although how or why I'm still quite uncertain.

Both good suggestions though and I thank you.

Regards,
Brad

 

[Brad Autry]

I'd wanted to follow up on the INDEX and MATCH method. I'm having trouble
getting it to work. I'm using the following formula:

=INDEX(Job,MATCH(1,(position=I2)*(begin>=G2)*(end<=G2),0))

Where:

Job is named range for column D on the lookup table, containing the job code
information
position is named range for column A on the lookup table, containing
position codes
I2 is the lookup value, a position code
begin is a named range for column C on the lookup table, containing the
range begin date
end is the named range for range end date

It is, essentially, what was suggested yesterday, only with named ranges. I
am entering it as an array formula.

Any ideas why it is not working? Thank you ahead of time.