looking up values in 1 column based on 2 other columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Try entered with Ctrl+Shift+Enter:

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

HTH
 
Thanks, I'm still getting #N/A, are there any limitations on the INDEX
function? As the result will be text.
 
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?
 
User error!!

Thanks, works spot on.

Chris

Toppers said:
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?
Click to expand...
 
Back
Top