INDEX and MATCH help
 

Greetings,

This is a follow up post to one I'd written yesterday.

The short of it is as follows:

I have a lookup table consisting of four columns:

A: Position code
B: Begin Date
C: End Date
D: Job Code

The job code may vary based on date ranges.

On a separate table, I am attempting to look up the job code for the
corresponding position code and date.

Based on one of the suggestions from here yesterday, I tried the following:

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

Whe

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


I am entering it as an array formula. Any ideas why it might not be
working?

Thank you ahead of time.

Regards,
Brad

INDEX and MATCH help
 

Hi,
try this assuming that your first sheet is called sheet1 and you enter the
formula in sheet2. Sheet2 column A has the job code and you want to get the
position code in B and the begin date in C

in B2 enter

=sumproduct(--(A2=sheet1!$D$1:$D$1000),sheet1!$A$1:$A$1000)

in C2 enter

=sumproduct(--(A2=sheet1!$D$1:$D$1000),sheet1!$B$1:$B$1000)


"Brad Autry" wrote:

Greetings,

This is a follow up post to one I'd written yesterday.

The short of it is as follows:

I have a lookup table consisting of four columns:

A: Position code
B: Begin Date
C: End Date
D: Job Code

The job code may vary based on date ranges.

On a separate table, I am attempting to look up the job code for the
corresponding position code and date.

Based on one of the suggestions from here yesterday, I tried the following:

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

Whe

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


I am entering it as an array formula. Any ideas why it might not be
working?

Thank you ahead of time.

Regards,
Brad