ISNA Help

[Guest]

Here is a formula that I have working (it is long with lots of IFs and
repeated many times in my spreadsheet):
=IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,22,0)="Remediate",1,IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,22,0)="Minimum",2,IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,22,0)="Superior",3,IF(VLOOKUP($B10,'[R1 Fitness
2007.xls]R'!$B$2:$W$20,9,0)="Opt - In","|",IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,9,0)="Opt - Out","O",IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,11,0)<>"","M",""))))))

Two Questions:
[1] How would I wrap ISNA around this?
[2] Is there a simpler way of writing the formula?

Thanks.

 

[Bernard Liengme]

You are looking up B10 in a table on ONE other file
(I assume "[R1 Fitness.xls" should be "AnotherFile.xls")
and returning 1,2,3, I or O
but you want to add: If B10 is not found return a blank

=IF(isna(VLOOKUP($B10,'[Another File.xls]R'!$B$2:$W$20,1,0)), "",
IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,22,0)="Remediate",1,IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,22,0)="Minimum",2,IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,22,0)="Superior",3,IF(VLOOKUP($B10,'[R1 Fitness
2007.xls]R'!$B$2:$W$20,9,0)="Opt - In","|",IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,9,0)="Opt - Out","O",IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,11,0)<>"","M","")))))) )

I have added another ) at the very end of your formula.

Note that you have now reached the limit of 7 nestings (unless you are using
XL2007).
Could you use a 'helper' column - it could be hidden?

Note my solution is untested.

 

[Guest]

Your assumption is correct, I did not change all fo the file name references
as I intended.

Your solution works as well. It is greatly appreciated!

I am using 2003 and am concerned that I at the limit for IF statements that
is why I was interested in another way of writing the formula.

You are looking up B10 in a table on ONE other file
(I assume "[R1 Fitness.xls" should be "AnotherFile.xls")
and returning 1,2,3, I or O
but you want to add: If B10 is not found return a blank

=IF(isna(VLOOKUP($B10,'[Another File.xls]R'!$B$2:$W$20,1,0)), "",
IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,22,0)="Remediate",1,IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,22,0)="Minimum",2,IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,22,0)="Superior",3,IF(VLOOKUP($B10,'[R1 Fitness
2007.xls]R'!$B$2:$W$20,9,0)="Opt - In","|",IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,9,0)="Opt - Out","O",IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,11,0)<>"","M","")))))) )

I have added another ) at the very end of your formula.

Note that you have now reached the limit of 7 nestings (unless you are using
XL2007).
Could you use a 'helper' column - it could be hidden?

Note my solution is untested.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Here is a formula that I have working (it is long with lots of IFs and
repeated many times in my spreadsheet):
=IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,22,0)="Remediate",1,IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,22,0)="Minimum",2,IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,22,0)="Superior",3,IF(VLOOKUP($B10,'[R1 Fitness
2007.xls]R'!$B$2:$W$20,9,0)="Opt - In","|",IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,9,0)="Opt - Out","O",IF(VLOOKUP($B10,'[Another
File.xls]R'!$B$2:$W$20,11,0)<>"","M",""))))))

Two Questions:
[1] How would I wrap ISNA around this?
[2] Is there a simpler way of writing the formula?

Thanks.