|
Suggestion about a formula
Hi:
I already find a way to use several IF on a formula, but now I have another question, is there a way that I can add 3 more IF to this formula but without showing me the error message of: "Formula is too long". Any suggestion to type this formual on another way?? Thanks for your help! Nelly. =IF(B21=A52,B52,"")&IF(B21=A53,B53,"")&IF(B21=A54, B54,"")&IF(B21=A55,B55,"")&IF(B21=A56,B56,"")&IF(B 21=A57,B57,"")&IF(B21=A58,B58,"")&IF(B21=A59,B59," ")&IF(B21=A60,B60,"")&IF(B21=A61,B61,"")&IF(B21=A6 2,B62,"")&IF(B21=A63,B63,"")&IF(B21=A64,B64,"")&IF (B21=A65,B65,"")&IF(B21=A66,B66,"")&IF(B21=A67,B67 ,"")&IF(B21=A68,B68,"")&IF(B21=A68,B68,"")&IF(B21= A69,B69,"")&IF(B21=A70,B70,"")&IF(B21=A71,B71,"")& IF(B21=A72,B72,"")&IF(B21=A73,B73,"")&IF(B21=A74,B 74,"")&IF(B21=A75,B75,"")&IF(B21=A76,B76,"")&IF(B2 1=A77,B77,"")&IF(B21=A78,B78,"")&IF(B21=A79,B79,"" )&IF(B21=A80,B80,"")&IF(B21=A81,B81,"")&IF(B21=A82 ,B82,"")&IF(B21=A83,B83,"")&IF(B21=A84,B84,"")&IF( B21=A85,B85,"")&IF(B21=A86,B86,"")&IF(B21=A87,B87, "")&IF(B21=A88,B88,"")&IF(B21=A89,B89,"")&IF(B21=A 90,B90,"")&IF(B21=A91,B91,"")&IF(B21=A92,B92,"")&I F(B21=A93,B93,"")&IF(B21=A94,B94,"")&IF(B21=A95,B9 5,"")&IF(B21=A96,B96,"")&IF(B21=A97,B97,"")&IF(B21 =A98,B98,"") |
Suggestion about a formula
Is that not just
=IF(ISERROR(VLOOKUP(B21,A52:D98,4,0)),"",VLOOKUP(B 21,A52:D98,4,0)) -- HTH RP (remove nothere from the email address if mailing direct) "Nelly" wrote in message ... Thanks a lot Elkar!!! Here is another that is similar, I tried to used the one you showed me but doesn't work. Could you, please, help me?? =IF(B21=A52,D52,"")&IF(B21=A53,D53,"")&IF(B21=A54, D54,"")&IF(B21=A55,D55,"") &IF(B21=A56,D56,"")&IF(B21=A57,D57,"")&IF(B21=A58, D58,"")&IF(B21=A59,D59,"") &IF(B21=A60,D60,"")&IF(B21=A61,D61,"")&IF(B21=A62, D62,"")&IF(B21=A63,D63,"") &IF(B21=A64,D64,"")&IF(B21=A65,D65,"")&IF(B21=A66, D66,"")&IF(B21=A67,D67,"") &IF(B21=A68,D68,"")&IF(B21=A68,D68,"")&IF(B21=A69, D69,"")&IF(B21=A70,D70,"") &IF(B21=A71,D71,"")&IF(B21=A72,D72,"")&IF(B21=A73, D73,"")&IF(B21=A74,D74,"") &IF(B21=A75,D75,"")&IF(B21=A76,D76,"")&IF(B21=A77, D77,"")&IF(B21=A78,D78,"") &IF(B21=A79,D79,"")&IF(B21=A80,D80,"")&IF(B21=A81, D81,"")&IF(B21=A82,D82,"") &IF(B21=A83,D83,"")&IF(B21=A84,D84,"")&IF(B21=A85, D85,"")&IF(B21=A86,D86,"") &IF(B21=A87,D87,"")&IF(B21=A88,D88,"")&IF(B21=A89, D89,"")&IF(B21=A90,D90,"") &IF(B21=A91,D91,"")&IF(B21=A92,D92,"")&IF(B21=A93, D93,"")&IF(B21=A94,D94,"") &IF(B21=A95,D95,"")&IF(B21=A96,D96,"")&IF(B21=A97, D97,"")&IF(B21=A98,D98,"") Thanks a lot!!!! "Elkar" wrote: I think this might work for you: =IF(ISERROR(VLOOKUP(B21,A52:B98,2,0)),"",VLOOKUP(B 21,A52:B98,2,0)) "Nelly" wrote: Hi: I already find a way to use several IF on a formula, but now I have another question, is there a way that I can add 3 more IF to this formula but without showing me the error message of: "Formula is too long". Any suggestion to type this formual on another way?? Thanks for your help! Nelly. =IF(B21=A52,B52,"")&IF(B21=A53,B53,"")&IF(B21=A54, B54,"")&IF(B21=A55,B55,"") &IF(B21=A56,B56,"")&IF(B21=A57,B57,"")&IF(B21=A58, B58,"")&IF(B21=A59,B59,"") &IF(B21=A60,B60,"")&IF(B21=A61,B61,"")&IF(B21=A62, B62,"")&IF(B21=A63,B63,"") &IF(B21=A64,B64,"")&IF(B21=A65,B65,"")&IF(B21=A66, B66,"")&IF(B21=A67,B67,"") &IF(B21=A68,B68,"")&IF(B21=A68,B68,"")&IF(B21=A69, B69,"")&IF(B21=A70,B70,"") &IF(B21=A71,B71,"")&IF(B21=A72,B72,"")&IF(B21=A73, B73,"")&IF(B21=A74,B74,"") &IF(B21=A75,B75,"")&IF(B21=A76,B76,"")&IF(B21=A77, B77,"")&IF(B21=A78,B78,"") &IF(B21=A79,B79,"")&IF(B21=A80,B80,"")&IF(B21=A81, B81,"")&IF(B21=A82,B82,"") &IF(B21=A83,B83,"")&IF(B21=A84,B84,"")&IF(B21=A85, B85,"")&IF(B21=A86,B86,"") &IF(B21=A87,B87,"")&IF(B21=A88,B88,"")&IF(B21=A89, B89,"")&IF(B21=A90,B90,"") &IF(B21=A91,B91,"")&IF(B21=A92,B92,"")&IF(B21=A93, B93,"")&IF(B21=A94,B94,"") &IF(B21=A95,B95,"")&IF(B21=A96,B96,"")&IF(B21=A97, B97,"")&IF(B21=A98,B98,"") |
Suggestion about a formula
=IF(ISERROR(VLOOKUP(B21,A52:D98,4,0)),"",VLOOKUP(B 21,A52:D98,4,0))
Notice the 4's in the VLOOKUP function. This pulls the results from the 4th column in the array (column D). "Nelly" wrote: Thanks a lot Elkar!!! Here is another that is similar, I tried to used the one you showed me but doesn't work. Could you, please, help me?? =IF(B21=A52,D52,"")&IF(B21=A53,D53,"")&IF(B21=A54, D54,"")&IF(B21=A55,D55,"")&IF(B21=A56,D56,"")&IF(B 21=A57,D57,"")&IF(B21=A58,D58,"")&IF(B21=A59,D59," ")&IF(B21=A60,D60,"")&IF(B21=A61,D61,"")&IF(B21=A6 2,D62,"")&IF(B21=A63,D63,"")&IF(B21=A64,D64,"")&IF (B21=A65,D65,"")&IF(B21=A66,D66,"")&IF(B21=A67,D67 ,"")&IF(B21=A68,D68,"")&IF(B21=A68,D68,"")&IF(B21= A69,D69,"")&IF(B21=A70,D70,"")&IF(B21=A71,D71,"")& IF(B21=A72,D72,"")&IF(B21=A73,D73,"")&IF(B21=A74,D 74,"")&IF(B21=A75,D75,"")&IF(B21=A76,D76,"")&IF(B2 1=A77,D77,"")&IF(B21=A78,D78,"")&IF(B21=A79,D79,"" )&IF(B21=A80,D80,"")&IF(B21=A81,D81,"")&IF(B21=A82 ,D82,"")&IF(B21=A83,D83,"")&IF(B21=A84,D84,"")&IF( B21=A85,D85,"")&IF(B21=A86,D86,"")&IF(B21=A87,D87, "")&IF(B21=A88,D88,"")&IF(B21=A89,D89,"")&IF(B21=A 90,D90,"")&IF(B21=A91,D91,"")&IF(B21=A92,D92,"")&I F(B21=A93,D93,"")&IF(B21=A94,D94,"")&IF(B21=A95,D9 5,"")&IF(B21=A96,D96,"")&IF(B21=A97,D97,"")&IF(B21 =A98,D98,"") Thanks a lot!!!! "Elkar" wrote: I think this might work for you: =IF(ISERROR(VLOOKUP(B21,A52:B98,2,0)),"",VLOOKUP(B 21,A52:B98,2,0)) "Nelly" wrote: Hi: I already find a way to use several IF on a formula, but now I have another question, is there a way that I can add 3 more IF to this formula but without showing me the error message of: "Formula is too long". Any suggestion to type this formual on another way?? Thanks for your help! Nelly. =IF(B21=A52,B52,"")&IF(B21=A53,B53,"")&IF(B21=A54, B54,"")&IF(B21=A55,B55,"")&IF(B21=A56,B56,"")&IF(B 21=A57,B57,"")&IF(B21=A58,B58,"")&IF(B21=A59,B59," ")&IF(B21=A60,B60,"")&IF(B21=A61,B61,"")&IF(B21=A6 2,B62,"")&IF(B21=A63,B63,"")&IF(B21=A64,B64,"")&IF (B21=A65,B65,"")&IF(B21=A66,B66,"")&IF(B21=A67,B67 ,"")&IF(B21=A68,B68,"")&IF(B21=A68,B68,"")&IF(B21= A69,B69,"")&IF(B21=A70,B70,"")&IF(B21=A71,B71,"")& IF(B21=A72,B72,"")&IF(B21=A73,B73,"")&IF(B21=A74,B 74,"")&IF(B21=A75,B75,"")&IF(B21=A76,B76,"")&IF(B2 1=A77,B77,"")&IF(B21=A78,B78,"")&IF(B21=A79,B79,"" )&IF(B21=A80,B80,"")&IF(B21=A81,B81,"")&IF(B21=A82 ,B82,"")&IF(B21=A83,B83,"")&IF(B21=A84,B84,"")&IF( B21=A85,B85,"")&IF(B21=A86,B86,"")&IF(B21=A87,B87, "")&IF(B21=A88,B88,"")&IF(B21=A89,B89,"")&IF(B21=A 90,B90,"")&IF(B21=A91,B91,"")&IF(B21=A92,B92,"")&I F(B21=A93,B93,"")&IF(B21=A94,B94,"")&IF(B21=A95,B9 5,"")&IF(B21=A96,B96,"")&IF(B21=A97,B97,"")&IF(B21 =A98,B98,"") |
Suggestion about a formula
Try this: =IF(ISERROR(VLOOKUP(B21,A52:D98,4,0)),"",VLOOKUP(B 21,A52:D98,4,0)) Good Luck Bruce -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=483620 |
| All times are GMT +1. The time now is 12:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com