Multiple IF statement - a better way?

revver

revver

Active Member
Joined
Dec 20, 2007
Messages
257
I have a range (row) of 26 cells (B3:AB3), any/all of which can contain a letter, upper or lower case.

In cell AD3 I want a formula which will display "A" if any cell in the range contains either "a" or "A" otherwise return a " ".

I know I could construct a IF(B3="A",IF(C3="A",IF(D3 ...
but isn't there a better formula like
IF((B3:AB3 contains "A") or (B3:AB3 contains "a"), "A", " ").
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">E</td><td style="text-align: center;;">H</td><td style="text-align: center;;">J</td><td style="text-align: center;;">k</td><td style="text-align: center;;">I</td><td style="text-align: center;;"> </td><td style="text-align: center;;">B</td><td style="text-align: center;;">J</td><td style="text-align: center;;">J</td><td style="text-align: center;;">A</td><td style="text-align: center;;">c</td><td style="text-align: center;;">f</td><td style="text-align: center;;">B</td><td style="text-align: center;;">l</td><td style="text-align: center;;"> </td><td style="text-align: center;;">H</td><td style="text-align: center;;">I</td><td style="text-align: center;;">A</td><td style="text-align: center;;">B</td><td style="text-align: center;;">E</td><td style="text-align: center;;"> </td><td style="text-align: center;;">l</td><td style="text-align: center;;">K</td><td style="text-align: center;;">A</td><td style="text-align: center;;">j</td><td style="text-align: center;;">j</td><td style="text-align: center;;"></td><td style="text-align: center;;">A</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">E</td><td style="text-align: center;;">H</td><td style="text-align: center;;">J</td><td style="text-align: center;;">k</td><td style="text-align: center;;">I</td><td style="text-align: center;;"> </td><td style="text-align: center;;">B</td><td style="text-align: center;;">J</td><td style="text-align: center;;">J</td><td style="text-align: center;;"> </td><td style="text-align: center;;">c</td><td style="text-align: center;;">f</td><td style="text-align: center;;">B</td><td style="text-align: center;;">l</td><td style="text-align: center;;"> </td><td style="text-align: center;;">H</td><td style="text-align: center;;">I</td><td style="text-align: center;;">a</td><td style="text-align: center;;">B</td><td style="text-align: center;;">E</td><td style="text-align: center;;"> </td><td style="text-align: center;;">l</td><td style="text-align: center;;">K</td><td style="text-align: center;;"> </td><td style="text-align: center;;">j</td><td style="text-align: center;;">j</td><td style="text-align: center;;"></td><td style="text-align: center;;">A</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">E</td><td style="text-align: center;;">H</td><td style="text-align: center;;">J</td><td style="text-align: center;;">k</td><td style="text-align: center;;">I</td><td style="text-align: center;;"> </td><td style="text-align: center;;">B</td><td style="text-align: center;;">J</td><td style="text-align: center;;">J</td><td style="text-align: center;;"> </td><td style="text-align: center;;">c</td><td style="text-align: center;;">f</td><td style="text-align: center;;">B</td><td style="text-align: center;;">l</td><td style="text-align: center;;"> </td><td style="text-align: center;;">H</td><td style="text-align: center;;">I</td><td style="text-align: center;;"></td><td style="text-align: center;;">B</td><td style="text-align: center;;">E</td><td style="text-align: center;;"> </td><td style="text-align: center;;">l</td><td style="text-align: center;;">K</td><td style="text-align: center;;"> </td><td style="text-align: center;;">j</td><td style="text-align: center;;">j</td><td style="text-align: right;;"></td><td style="text-align: center;;"> </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AD3</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">C3:AB3,"A"</font>),"A"," "</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0
revver said:
I have a range (row) of 26 cells (B3:AB3), any/all of which can contain a letter, upper or lower case.

In cell AD3 I want a formula which will display "A" if any cell in the range contains either "a" or "A" otherwise return a " ".

I know I could construct a IF(B3="A",IF(C3="A",IF(D3 ...
but isn't there a better formula like
IF((B3:AB3 contains "A") or (B3:AB3 contains "a"), "A", " ").
Click to expand...
Try it like this...

=IF(COUNTIF(C3:AB3,"A"),"A","")

Note the "" in the formula. This is different from what you show in your explanation.

This " " will return a space character while this "" will return an empty text string so the cell appears blank.

If you need to do downstream calculations based on these count cells either containing a number or being blank then those space characters could cause a problem.
 
Upvote 0
Revver,

Another way:

Note: with " " or with "" - you choose what you want.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">E</td><td style="text-align: center;;">H</td><td style="text-align: center;;">J</td><td style="text-align: center;;">k</td><td style="text-align: center;;">I</td><td style="text-align: center;;"> </td><td style="text-align: center;;">B</td><td style="text-align: center;;">J</td><td style="text-align: center;;">J</td><td style="text-align: center;;">A</td><td style="text-align: center;;">c</td><td style="text-align: center;;">f</td><td style="text-align: center;;">B</td><td style="text-align: center;;">l</td><td style="text-align: center;;"> </td><td style="text-align: center;;">H</td><td style="text-align: center;;">I</td><td style="text-align: center;;">A</td><td style="text-align: center;;">B</td><td style="text-align: center;;">E</td><td style="text-align: center;;"> </td><td style="text-align: center;;">l</td><td style="text-align: center;;">K</td><td style="text-align: center;;">A</td><td style="text-align: center;;">j</td><td style="text-align: center;;">j</td><td style="text-align: center;;"></td><td style="text-align: center;;">A</td><td style="text-align: center;;">A</td><td style="text-align: center;;">A</td><td style="text-align: center;;">A</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">E</td><td style="text-align: center;;">H</td><td style="text-align: center;;">J</td><td style="text-align: center;;">k</td><td style="text-align: center;;">I</td><td style="text-align: center;;"> </td><td style="text-align: center;;">B</td><td style="text-align: center;;">J</td><td style="text-align: center;;">J</td><td style="text-align: center;;"> </td><td style="text-align: center;;">c</td><td style="text-align: center;;">f</td><td style="text-align: center;;">B</td><td style="text-align: center;;">l</td><td style="text-align: center;;"> </td><td style="text-align: center;;">H</td><td style="text-align: center;;">I</td><td style="text-align: center;;">a</td><td style="text-align: center;;">B</td><td style="text-align: center;;">E</td><td style="text-align: center;;"> </td><td style="text-align: center;;">l</td><td style="text-align: center;;">K</td><td style="text-align: center;;"> </td><td style="text-align: center;;">j</td><td style="text-align: center;;">j</td><td style="text-align: center;;"></td><td style="text-align: center;;">A</td><td style="text-align: center;;">A</td><td style="text-align: center;;">A</td><td style="text-align: center;;">A</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">E</td><td style="text-align: center;;">H</td><td style="text-align: center;;">J</td><td style="text-align: center;;">k</td><td style="text-align: center;;">I</td><td style="text-align: center;;"> </td><td style="text-align: center;;">B</td><td style="text-align: center;;">J</td><td style="text-align: center;;">J</td><td style="text-align: center;;"> </td><td style="text-align: center;;">c</td><td style="text-align: center;;">f</td><td style="text-align: center;;">B</td><td style="text-align: center;;">l</td><td style="text-align: center;;"> </td><td style="text-align: center;;">H</td><td style="text-align: center;;">I</td><td style="text-align: center;;"></td><td style="text-align: center;;">B</td><td style="text-align: center;;">E</td><td style="text-align: center;;"> </td><td style="text-align: center;;">l</td><td style="text-align: center;;">K</td><td style="text-align: center;;"> </td><td style="text-align: center;;">j</td><td style="text-align: center;;">j</td><td style="text-align: right;;"></td><td style="text-align: center;;"> </td><td style="text-align: center;;"></td><td style="text-align: center;;"> </td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AD3</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">"A",C3:AB3,0</font>)</font>),"A"," "</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AE3</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">"A",C3:AB3,0</font>)</font>),"A",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AF3</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">C3:AB3,"A"</font>),"A"," "</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AG3</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">C3:AB3,"A"</font>),"A",""</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0
alternatly =IF(ISNUMBER(MATCH("*A*",C3:AB3,0)),"A"," ") will trigger if any cell has an A anywhere, eg "CAT" will trigger it.
 
Upvote 0
markmzz said:
Revver,

Another way:

Note: with " " or with "" - you choose what you want.

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH><TH>N</TH><TH>O</TH><TH>P</TH><TH>Q</TH><TH>R</TH><TH>S</TH><TH>T</TH><TH>U</TH><TH>V</TH><TH>W</TH><TH>X</TH><TH>Y</TH><TH>Z</TH><TH>AA</TH><TH>AB</TH><TH>AC</TH><TH>AD</TH><TH>AE</TH><TH>AF</TH><TH>AG</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">E</TD><TD style="TEXT-ALIGN: center">H</TD><TD style="TEXT-ALIGN: center">J</TD><TD style="TEXT-ALIGN: center">k</TD><TD style="TEXT-ALIGN: center">I</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">J</TD><TD style="TEXT-ALIGN: center">J</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center">c</TD><TD style="TEXT-ALIGN: center">f</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">l</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">H</TD><TD style="TEXT-ALIGN: center">I</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">E</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">l</TD><TD style="TEXT-ALIGN: center">K</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center">j</TD><TD style="TEXT-ALIGN: center">j</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center">A</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">E</TD><TD style="TEXT-ALIGN: center">H</TD><TD style="TEXT-ALIGN: center">J</TD><TD style="TEXT-ALIGN: center">k</TD><TD style="TEXT-ALIGN: center">I</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">J</TD><TD style="TEXT-ALIGN: center">J</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">c</TD><TD style="TEXT-ALIGN: center">f</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">l</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">H</TD><TD style="TEXT-ALIGN: center">I</TD><TD style="TEXT-ALIGN: center">a</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">E</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">l</TD><TD style="TEXT-ALIGN: center">K</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">j</TD><TD style="TEXT-ALIGN: center">j</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center">A</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">E</TD><TD style="TEXT-ALIGN: center">H</TD><TD style="TEXT-ALIGN: center">J</TD><TD style="TEXT-ALIGN: center">k</TD><TD style="TEXT-ALIGN: center">I</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">J</TD><TD style="TEXT-ALIGN: center">J</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">c</TD><TD style="TEXT-ALIGN: center">f</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">l</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">H</TD><TD style="TEXT-ALIGN: center">I</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">E</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">l</TD><TD style="TEXT-ALIGN: center">K</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">j</TD><TD style="TEXT-ALIGN: center">j</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD></TR></TBODY></TABLE>
Sheet2​


<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>AD3</TH><TD style="TEXT-ALIGN: left">=IF(ISNUMBER(MATCH("A",C3:AB3,0)),"A"," ")</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>AE3</TH><TD style="TEXT-ALIGN: left">=IF(ISNUMBER(MATCH("A",C3:AB3,0)),"A","")</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>AF3</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF(C3:AB3,"A"),"A"," ")</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>AG3</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF(C3:AB3,"A"),"A","")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz
Click to expand...
Saves a couple of keystrokes.

=IF(COUNT(MATCH("A",C3:AB3,0)),"A","")
 
Upvote 0
Thanks guys,
Such a raft of responses I wasn't expecting.

I haven't tried any of them yet but I am sure I will solve my problem now.

I'ts time to come clean. This is for a sudoku-like grid constructor, but with letters not digits. A grid of 26x26 rather than 9x9. A given letter must appear once and only once in each row and once and only once in each column. Each row and each column must contain the entire alphabet of 26 letters.

I want excel to help me by showing which letters have been used in each row and column. I'll probably switch and have it show me which ones have not been used.

Thanks again for the help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
excel count "√"
Replies
9
Views
243
Fluff
Fluff
F
  • Question Question
Replace all IFERROR & INDEX & MATCH formulas with XLOOKUP
Replies
4
Views
646
Fabulist
F
G
  • Question Question
Find Duplicate Values using a Criteria
Replies
2
Views
208
Eric W
Eric W
H
  • Question Question
Generate a table of Win, Loss, Tie results from a separate table of numerical results.
Replies
3
Views
408
arun_eagertolearn
A
D
  • Solved
Matching TODAY() using VLOOKUP
Replies
7
Views
246
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,599
Messages
6,179,827
Members
452,946
Latest member
JoseDavid

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top