looking up values in 1 column based on 2 other columns
 

Hi,

I've been trying to use the INDEX function to lookup values in 1 column
based on 2 others. The formula I have is below, but the result I'm getting
is "#N/A". I have confirmed the formula using Ctrl+Shift+Enter, but that
doesn't seem to have helped.

Formula -
=INDEX(Amendments!$G$2:$G$10000,MATCH($C$10&$C$21, Amendments!$A$2:$A$10000&Amendments!$F$2:$F$10000, 0))

C10 is a field containing a number and C21 is a field containing a date.

All help will be much appreciated!

Thx

Chris

looking up values in 1 column based on 2 other columns
 

Try entered with Ctrl+Shift+Enter:

=INDEX(Amendments!$G$2:$G$10000,MATCH(1,($C$10=Ame ndments!$A$2:$A$10000)*(C21=Amendments!$F$2:$F$100 00),0))

HTH

"CMO" wrote:

Hi,

I've been trying to use the INDEX function to lookup values in 1 column
based on 2 others. The formula I have is below, but the result I'm getting
is "#N/A". I have confirmed the formula using Ctrl+Shift+Enter, but that
doesn't seem to have helped.

Formula -
=INDEX(Amendments!$G$2:$G$10000,MATCH($C$10&$C$21, Amendments!$A$2:$A$10000&Amendments!$F$2:$F$10000, 0))

C10 is a field containing a number and C21 is a field containing a date.

All help will be much appreciated!

Thx

Chris

looking up values in 1 column based on 2 other columns
 

Thanks, I'm still getting #N/A, are there any limitations on the INDEX
function? As the result will be text.

"Toppers" wrote:

Try entered with Ctrl+Shift+Enter:

=INDEX(Amendments!$G$2:$G$10000,MATCH(1,($C$10=Ame ndments!$A$2:$A$10000)*(C21=Amendments!$F$2:$F$100 00),0))

HTH

"CMO" wrote:

Hi,

I've been trying to use the INDEX function to lookup values in 1 column
based on 2 others. The formula I have is below, but the result I'm getting
is "#N/A". I have confirmed the formula using Ctrl+Shift+Enter, but that
doesn't seem to have helped.

Formula -
=INDEX(Amendments!$G$2:$G$10000,MATCH($C$10&$C$21, Amendments!$A$2:$A$10000&Amendments!$F$2:$F$10000, 0))

C10 is a field containing a number and C21 is a field containing a date.

All help will be much appreciated!

Thx

Chris

looking up values in 1 column based on 2 other columns
 

It works OK for me; my sample data .... A2=21,F2=01/08/2006,
C10=21,C21=01/08/2006

Are you sure all the data is the correct format e.g. not comparing numbers
with text?

"CMO" wrote:

Thanks, I'm still getting #N/A, are there any limitations on the INDEX
function? As the result will be text.

"Toppers" wrote:

Try entered with Ctrl+Shift+Enter:

=INDEX(Amendments!$G$2:$G$10000,MATCH(1,($C$10=Ame ndments!$A$2:$A$10000)*(C21=Amendments!$F$2:$F$100 00),0))

HTH

"CMO" wrote:

Hi,

I've been trying to use the INDEX function to lookup values in 1 column
based on 2 others. The formula I have is below, but the result I'm getting
is "#N/A". I have confirmed the formula using Ctrl+Shift+Enter, but that
doesn't seem to have helped.

Formula -
=INDEX(Amendments!$G$2:$G$10000,MATCH($C$10&$C$21, Amendments!$A$2:$A$10000&Amendments!$F$2:$F$10000, 0))

C10 is a field containing a number and C21 is a field containing a date.

All help will be much appreciated!

Thx

Chris

looking up values in 1 column based on 2 other columns
 

User error!!

Thanks, works spot on.

Chris

"Toppers" wrote:

It works OK for me; my sample data .... A2=21,F2=01/08/2006,
C10=21,C21=01/08/2006

Are you sure all the data is the correct format e.g. not comparing numbers
with text?

"CMO" wrote:

Thanks, I'm still getting #N/A, are there any limitations on the INDEX
function? As the result will be text.

"Toppers" wrote:

Try entered with Ctrl+Shift+Enter:

=INDEX(Amendments!$G$2:$G$10000,MATCH(1,($C$10=Ame ndments!$A$2:$A$10000)*(C21=Amendments!$F$2:$F$100 00),0))

HTH

"CMO" wrote:

Hi,

I've been trying to use the INDEX function to lookup values in 1 column
based on 2 others. The formula I have is below, but the result I'm getting
is "#N/A". I have confirmed the formula using Ctrl+Shift+Enter, but that
doesn't seem to have helped.

Formula -
=INDEX(Amendments!$G$2:$G$10000,MATCH($C$10&$C$21, Amendments!$A$2:$A$10000&Amendments!$F$2:$F$10000, 0))

C10 is a field containing a number and C21 is a field containing a date.

All help will be much appreciated!

Thx

Chris

looking up values in 1 column based on 2 other columns
 

Good! and thank you for the feedback.

"CMO" wrote:

User error!!

Thanks, works spot on.

Chris

"Toppers" wrote:

It works OK for me; my sample data .... A2=21,F2=01/08/2006,
C10=21,C21=01/08/2006

Are you sure all the data is the correct format e.g. not comparing numbers
with text?

"CMO" wrote:

Thanks, I'm still getting #N/A, are there any limitations on the INDEX
function? As the result will be text.

"Toppers" wrote:

Try entered with Ctrl+Shift+Enter:

=INDEX(Amendments!$G$2:$G$10000,MATCH(1,($C$10=Ame ndments!$A$2:$A$10000)*(C21=Amendments!$F$2:$F$100 00),0))

HTH

"CMO" wrote:

Hi,

I've been trying to use the INDEX function to lookup values in 1 column
based on 2 others. The formula I have is below, but the result I'm getting
is "#N/A". I have confirmed the formula using Ctrl+Shift+Enter, but that
doesn't seem to have helped.

Formula -
=INDEX(Amendments!$G$2:$G$10000,MATCH($C$10&$C$21, Amendments!$A$2:$A$10000&Amendments!$F$2:$F$10000, 0))

C10 is a field containing a number and C21 is a field containing a date.

All help will be much appreciated!

Thx

Chris