help with a formula

[findyourgun]

I'm having trouble writing a formula and was wondering if someone could help me. I have a list of set numbers in column A, and in column B, I want to be able to have a value automatically filled according to the number in the corresponding cell in column A.

So... if A1 is 1100, B1 is Adult; if A1 is 1200, B1 is Media; if A1 is 1300, B1 is Youth... and so forth. Only, there's about 15 different for both columns (department numbers and the department names, fyi).

Any and all help is very appreciated. I need to make doing month end reports easier before my head explodes, lol.

Thanks in advance!

 

[T. Valko]

I'm having trouble writing a formula and was wondering if someone could help me. I have a list of set numbers in column A, and in column B, I want to be able to have a value automatically filled according to the number in the corresponding cell in column A.

So... if A1 is 1100, B1 is Adult; if A1 is 1200, B1 is Media; if A1 is 1300, B1 is Youth... and so forth. Only, there's about 15 different for both columns (department numbers and the department names, fyi).

Any and all help is very appreciated. I need to make doing month end reports easier before my head explodes, lol.

Thanks in advance!

See this...

http://contextures.com/xlFunctions02.html

 

[findyourgun]

See this...

http://contextures.com/xlFunctions02.html

I don't mean to be dense, but this is making no sense to me. If you or someone else could just get me started, then I can figure it out, but I'm not even sure where I should be putting my values in the formula.

 

[crook_101]

Hi,

Create a new sheet in your workbook, in the example below sheet 2 as follows:

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></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Number</TD><TD style="TEXT-ALIGN: center">Text</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">1100</TD><TD style="TEXT-ALIGN: center">Adult</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1200</TD><TD style="TEXT-ALIGN: center">Media</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">1300</TD><TD style="TEXT-ALIGN: center">Youth</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">1400</TD><TD style="TEXT-ALIGN: center">etc</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">1500</TD><TD style="TEXT-ALIGN: center">etc</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">1600</TD><TD style="TEXT-ALIGN: center">etc</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">1700</TD><TD style="TEXT-ALIGN: center">etc</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">1800</TD><TD style="TEXT-ALIGN: center">etc</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">1900</TD><TD style="TEXT-ALIGN: center">etc</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">etc</TD><TD style="TEXT-ALIGN: center">etc</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">etc</TD><TD style="TEXT-ALIGN: center">etc</TD></TR></TBODY></TABLE>

Sheet2​

Next in your master/main sheet you need the following formula in B2 in the example below:

=VLOOKUP(A2,Sheet2!A2:B30,2,0)



Copy this down as far as you need to produce the following results:

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Header</td><td style="text-align: center;;">Header</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1200</td><td style="text-align: center;;">Media</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1300</td><td style="text-align: center;;">Youth</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">1100</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">etc etc</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">Sheet1</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">B2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A2,Sheet2!A2:B30,2,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A3,Sheet2!A3:B31,2,0</font>)</td></tr></tbody></table></td></tr></table><br />

Cheers,
Ian

 

[indiantrix]

Crook's formula is almost correct...need to modify it
as follows to permit proper functioning when copying down:
=VLOOKUP(A2,Sheet2!$A$2:$B$30,2,0)
Larry.