Is there any limitation for if function ?

Hi all,

Can anyone enlighten me on the following issue.

I've a long list of value with indicator which I need to compare with an entry after which it need to return a text indicator. I'm using IF and MATCH function.

Following is the code I use:

=(IF(MATCH(C1,$D$5:$D$29,0)<=5,$E$9,IF(MATCH(C1,$D$5:$D$29,0)<=10,$E$14,IF(MATCH(C1,$D$5:$D$29,0)<=15,$E$19,IF(MATCH(C1,$D$5:$D$29,0)<=20,$E$24,IF(MATCH(C1,$D$5:$D$29,0)<=25,"Not Found"))))))

I've attached an excel file with an sample.

Btw, Is there anyway I can omit the length of IF & MATCH function by using other function.

Pls advice. Thanks
Marc

One way:

Please Login or Register  to view this content.

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula

The issue wasn't with the IF functions (in Excel 2003 you can have a max of 7 nested IF's, by the way). The error was coming with your MATCH function. Any number in C1 greater than 25 was generating an error in the first IF condition, and causing the entire formula to error.

One non-array version (no need for CTRL+SHIFT+ENTER) that should fix this is:

=IF(ISNA(MATCH(C1,D5:D29,0)),"Not Found",LOOKUP(MATCH(C1,D5:D29,0),{1,6,11,16,21},{"Test 1","Test 2","Test 3","Test 4","Test 5"}))

NBVC, yours still seems to error if the user enters 26 (or any number greater than 25).

Originally Posted by pjoaquin

...


NBVC, yours still seems to error if the user enters 26 (or any number greater than 25).

Easily remedied with an error trap:

Please Login or Register  to view this content.

CSE confirmed once again