INDEX/MATCH #REF! error

T

Tony

I have a worksheet pulls values from a source worksheet ('Jurisdiction
Entries'!$C$2:$C$35) based on a date I fill in on the target worksheet (A5)
and a state code (B3..). It brings in a couple of fields when it matches the
date ('Jurisdiction Entries'!$A$2:$A$35,0) and state code
(Entries'!$B$2:$B$35,0) in the source worksheet. When I cuse 09/30/2002 as my
date, it works fine. When I change to another date such as 09/30/2008, I get
a reference error (#REF!).

Here is the formula:

=INDEX('Jurisdiction Entries'!$C$2:$C$35,MATCH($A5,'Jurisdiction
Entries'!$B$2:$B$35,0),MATCH($B$3,'Jurisdiction Entries'!$A$2:$A$35,0))


Target Document
PERIOD ENDING: Tuesday, September 30, 2008 MATCH (A5)
(B3)
CT #REF! #REF! 0.0000%


Source Document
(A2:A35) (B2:B35) (C2:C35)
09/30/2002 WI 100
09/30/2002 WV 200
09/30/2008 CT 300
09/30/2008 IL 400


Could someone tell what I have to to be able to just change the date and
pick up the associated data.

Thanks

Tony D.
 
T

T. Valko

Try this:

=SUMPRODUCT(--('Jurisdiction Entries'!$A$2:$A$35=$B$3),--('Jurisdiction
Entries'!$B$2:$B$35=$A5),'Jurisdiction Entries'!$C$2:$C$35)
 
B

Billy Liddel

Tony

This is an array function entered CSE (Ctr, Shift, Enter)

=IF(ISERROR(MATCH($A$5&$B$5,'Jurisdiction Entries'!$B$2:$B$35&'Jurisdiction
Entries'!$A$2:$A$35,0)),"",INDEX('Jurisdiction
Entries'!$C$2:$C$35,MATCH($A$5&$B$5,'Jurisdiction
Entries'!$B$2:$B$35&'Jurisdiction Entries'!$A$2:$A$35,0)))

Excel puts curly brackets in the formula when it has been entered correctly.
Be careful abount page breaks when you copy the formula.

Regards
Peter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top