Help with formula.

[PaulKav]

Hi,

I'm a newbie looking for help with a formula in excel 2003.

I have a number of mutually exclusive columns (only one cell in each row will be populated) and I need a formula in the first column to return the column name depending on which cell in that row is not blank. I cannot use nested if's as there are more than 8 columns.

I would appreciate any help.

Thanks.

 

[MrKowz]

Welcome to the forums!

Something like this?

Excel 2003<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></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Item 1</TD><TD>Item 2</TD><TD>Item 3</TD><TD>Item 4</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>x</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Item 1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>x</TD><TD>Item 4</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right"></TD><TD>x</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Item 2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>x</TD><TD style="TEXT-ALIGN: right"></TD><TD>Item 3</TD></TR></TBODY></TABLE><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><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet2</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<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>E2</TH><TD style="TEXT-ALIGN: left">=INDEX($A$1:$D$1,MATCH("zzzzzz",A2:D2))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Or if they are populated with numbers:
<b>Excel 2003</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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Item 1</td><td style=";">Item 2</td><td style=";">Item 3</td><td style=";">Item 4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Item 1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style=";">Item 4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Item 2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style=";">Item 3</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><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">E2</th><td style="text-align:left">=INDEX(<font color="Blue">$A$1:$D$1,MATCH(<font color="Red">9.99E+307,A2:D2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[NBVC]

If you are entering numbers in those cells,

Try

=Lookup(9.9999999E+307,B2:Z2,B$1:Z$1)

If you are entering text

=Lookup(Rept("z",255),B2:Z1,B$1:Z$1)

adjust ranges to suit and copy down.

 

[AlphaFrog]

Yet one more way...

Excel Workbook
	A	B	C	D	E
1		Jan	Feb	Mar	Apr
2	Feb		x	x
3	Apr				x
4	Mar			x
5	Jan	x
Sheet

 

[PaulKav]

Works perfectly. Thanks for your help.

Was banging my head of a wall before I came across this forum.

Cheers