=INDEX(Sheet2!$C$1:$C$1405,MATCH(1,(Sheet2!$A$1:$A $1405=E4)*(Sheet2!$D$1:$D$1405=I4),0))
well .. if you did array-enter the above correctly earlier, and it still
returned #N/A, then there's some data inconsistency somewhere throwing up
the non-matches in either the lookup values in E4 and/or I4 versus the
values within the corresponding lookup ranges: Sheet2!$A$1:$A$1405,
Sheet2!$D$1:$D$1405
Try this revision to your formula in J4 (remember to array-enter):
=INDEX(Sheet2!$C$1:$C$1405,MATCH(1,(TRIM(Sheet2!$A $1:$A$1405)=TRIM(E4))*(TRIM(Sheet2!$D$1:$D$1405)=T RIM(I4)),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Little Penny" wrote in message
...
Hey thanks for the help I know I'm getting close just can't get it to
work. This is my first time using index function.
=INDEX(Sheet2!$C$1:$C$1405,MATCH(1,(Sheet2!$A$1:$A $1405=E4)*(Sheet2!$D$1:$D$1405=I4),0))
I get #NA and I think it should work because on sheet 2 A26=E4 and
D26=I4 it should give me the value in cell C26 but I get #N/A in cell
J4 of sheet 1 where I entered the formula.
Any suggestions
Thanks