If Function

=IF(ISNA(MATCH(Z2,$W$2:$W$10,0)),1,INDEX($W$2:$X$10,MATCH(Z2,$W$2:$W$10,0),2))

this worked for me.... thanks.

so is it impossible to have more than 8 statements in an if function?

Yes, there is a max number of nested IF statements. However, you can work around this by doing something like this:

=IF(Z3=W2,X2,0)+IF(Z3=W3,X3,0)+IF(Z3=W4,X4,0)+IF(Z3=W5,X5,0)+IF(Z3=W6,X6,0)+IF(Z3=W7,X7,0)+IF(Z3=W8,X8,0)+IF(Z3=W9,X9,0)+IF(Z3=W10,X10,0)

HTH

Jason

It still is not working. I am using whole numbers and desamuls =IF(C12=1,0)+IF(C12=6,6)+IF(C12=11.75,11.75)+IF(C12=23.25,23.25)+IF(C12=29,29)+IF(C12=35,35)+IF(C12=40.75,40.75)
this returns a 0 value.
I have tryed adding a less than
=IF(C11<=1,0)+IF(C11<=6,6)+IF(C11<=11.75,11.75)+IF(C11<=23.25,23.25)+IF(C11<=29,29)+IF(C11<=35,35)+IF(C11<=40.75,40.75)
this returns a 145.75 value witch i think is the sum of the brackets.
any other ideas

Timrmason, the replies in this thread were intended for etsusurveyor. As it states in the forum rules, you must start your own thread if you have a question. Please begin a new thread.

Jason