IF formula, multiple conditions, output text

[cwillsc1]

I am trying to write a formula that will allow me to out put a specified text value from one cell but am receiving an error message #VALUE!. I can only get this to work if the values in the cells are numbers. Any suggestions?

 

[jeffreybrown]

Hi and Welcome to the Board,

What formula have you tried?

 

[VoG]

Hello and welcome to MrExcel.

Try posting what you have in the cell, what the result of the formula should be and your current formula.

 

[cwillsc1]

I am trying to look at cell B7 and if it is equal to cell B52 through B73 it will output the corresponding cell in D52 through D73. It works correctly as long as the values in D52 through D73 are numbers, but if the value is a text I recieve #VALUE! as an output

Here is the formula

=IF(B7=B52,D52,IF(B7=B53,D53)+IF(B7=B54,D54)+IF(B7=B55,D55)+IF(B7=B56,D56)+IF(B7=B57,D57)+IF(B7=B58,D58)+IF(B7=B59,D59)+IF(B7=B60,D30)+IF(B7=B61,D61)+IF(B7=B62,D62)+IF(B7=B63,D63)+IF(B7=B64,D64)+IF(B7=B65,D65)+IF(B7=B66,D66)+IF(B7=B67,D67)+IF(B7=B68,D68)+IF(B7=B69,D69)+IF(B7=B70,D70)+IF(B7=B71,D71)+IF(B7=B72,D72)+IF(B7=B73,D73))

 

[VoG]

Try replacing + with &

 

[cwillsc1]

I tried replacing+ with & and now I receive #NAME? for every selection

 

[VoG]

It seems to work for me

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">D</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td >BHX</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td >V</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >52</td><td >K</td><td > </td><td >R</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >53</td><td >T</td><td > </td><td >Z</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >54</td><td >B</td><td > </td><td >K</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >55</td><td >V</td><td > </td><td >B</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >56</td><td >J</td><td > </td><td >L</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >57</td><td >F</td><td > </td><td >E</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >58</td><td >V</td><td > </td><td >H</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >59</td><td >K</td><td > </td><td >K</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >60</td><td >J</td><td > </td><td >M</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >61</td><td >N</td><td > </td><td >T</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >62</td><td >M</td><td > </td><td >S</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >63</td><td >B</td><td > </td><td >C</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >64</td><td >S</td><td > </td><td >S</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >65</td><td >D</td><td > </td><td >A</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >66</td><td >X</td><td > </td><td >V</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >67</td><td >V</td><td > </td><td >X</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >68</td><td >F</td><td > </td><td >K</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >69</td><td >K</td><td > </td><td >G</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >70</td><td >D</td><td > </td><td >H</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >71</td><td >P</td><td > </td><td >O</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >72</td><td >D</td><td > </td><td >Q</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >73</td><td >A</td><td > </td><td >C</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=IF(B7=B52,D52,IF<span style=' color:008000; '>(B7=B53,D53,"")</span>&IF<span style=' color:008000; '>(B7=B54,D54,"")</span>&IF<span style=' color:008000; '>(B7=B55,D55,"")</span>&IF<span style=' color:008000; '>(B7=B56,D56,"")</span>&IF<span style=' color:008000; '>(B7=B57,D57,"")</span>&IF<span style=' color:008000; '>(B7=B58,D58,"")</span>&IF<span style=' color:008000; '>(B7=B59,D59,"")</span>& IF<span style=' color:008000; '>(B7=B60,D30,"")</span>&IF<span style=' color:008000; '>(B7=B61,D61,"")</span>&IF<span style=' color:008000; '>(B7=B62,D62,"")</span>&IF<span style=' color:008000; '>(B7=B63,D63,"")</span>&IF<span style=' color:008000; '>(B7=B64,D64,"")</span>&IF<span style=' color:008000; '>(B7=B65,D65,"")</span>&IF<span style=' color:008000; '>(B7=B66,D66,"")</span>&IF<span style=' color:008000; '>(B7=B67,D67,"")</span>& IF<span style=' color:008000; '>(B7=B68,D68,"")</span>&IF<span style=' color:008000; '>(B7=B69,D69,"")</span>&IF<span style=' color:008000; '>(B7=B70,D70,"")</span>&IF<span style=' color:008000; '>(B7=B71,D71,"")</span>&IF<span style=' color:008000; '>(B7=B72,D72,"")</span>&IF<span style=' color:008000; '>(B7=B73,D73,"")</span>)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[cwillsc1]

Thanks, it works I didnt have the quotation marks. I see why your the excel MVP!