If Function

it outputs 1 when this particular value is 6
attached is the actual page. if Name1=Name1 then 10. but i need it to run through all of the names incase the name in Z2 is Name 10, then output 1

Print screens or the actual Excel file always help. You were using Z3 in your original formula, so that is what we assumed you needed. Instead, try using Z2:

=IF(ISNA(MATCH(Z2,W2:W10,0)),1,INDEX(W2:X10,MATCH(Z2,W2:W10,0),2))

Jason

Try this one

if(iserror(vlookup(Z3,W2:X13,2,FALSE)),1,Z3)

I expect it works because I use Spanish Excel and I had to look for the English formulas.

I can not seem to get it to work.
What I am trying to accomplish is
I input the actual opening width in C5 and Excel finds the width size that is the same or the next size lager.
Example
The width is 20, which is between 17.6 &23.5 on the Shutter size chart, I enter that in C5 excel returns the value of 30.25 from line 4 on the Shutter size chart.
So I think should be looking for a equal to or greater than type formula but there are so many arguments

=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