If Function

[etsusurveyor]

i do not know how to put more than 8 tests in one cell
i need to put 9 with 1 as the false result
ive also tried with all the parenthesis at the end.

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

[jasoncw]

See if this formula will work for you:

=IF(ISNA(MATCH(Z3,W2:W10,0)),1,IF(ISNA(MATCH(Z3,W2:W9,0)),X10,INDEX(W2:X9,MATCH(Z3,W2:W9,0),2)))

HTH

Jason

[daddylonglegs]

Perhaps just

=IF(ISNA(MATCH(Z3,W2:W10,0)),1, VLOOKUP(Z3,W2:X10,2,0))

[etsusurveyor]

what if we take away the need for output of 1 for a false statement and have
what i have are a list of 10 names in column "W", list of 10 to 1 in column "X".
i want to display the number in column "X" if name in column "Z" matches name left of number.

=IF(Z4=W3,X3),IF(Z4=W4,X4),IF(Z4=W5,X5),IF(Z4=W6,X6),IF(Z4=W7,X7),IF(Z4=W8,X8),IF(Z4=W9,X9),IF(Z4=W10,X10),IF(Z4=W11,X11,1)

[timrmason]

I have a similar problem
I am looking for a solution to this spread sheet. A little back ground mite help to under stand what I am trying to do.
I supply and install Accordion shutters, the shutter widths only come in certain sizes. (see ShutSizeChart.pdf) and the building openings varies. When I order, I need to go to the next size larger than the window. The problem is the nesting limit; the size chart contains 70 sizes.
See ShutWorkSh.pdf

[jasoncw]

Try this one instead:

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

Jason

what if we take away the need for output of 1 for a false statement and have
what i have are a list of 10 names in column "W", list of 10 to 1 in column "X".
i want to display the number in column "X" if name in column "Z" matches name left of number.

[etsusurveyor]

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

[jasoncw]

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

[timrmason]

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

[etsusurveyor]

=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?