Index Function returning #VALUE

[lsteffen]

Good afternoon,

I have a worksheet that needs to populate an account number based on a unit number. I have a separate sheet with all of the account numbers listed. I have my formula written as such in A1:

=INDEX(B1, AcctNoLookup!A1:A22,0)

The value in B1 is: CD892 (cd892 is preceeded with spaces)
The value in A1 should be: CD

Can someone help with what I am doing wrong? I have used CNTROL-SHIFT-ENTER on A1.

Thank you,
Liz

 

[markmzz]

lsteffen,

Could you post examples of yours sheets?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

Use the link below for helper to post.

http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2

Markmzz

 

[T. Valko]

Good afternoon,

I have a worksheet that needs to populate an account number based on a unit number. I have a separate sheet with all of the account numbers listed. I have my formula written as such in A1:

=INDEX(B1, AcctNoLookup!A1:A22,0)

The value in B1 is: CD892 (cd892 is preceeded with spaces)
The value in A1 should be: CD

Can someone help with what I am doing wrong? I have used CNTROL-SHIFT-ENTER on A1.

Thank you,
Liz

See if this is what you had in mind:

http://contextures.com/xlFunctions02.html

 

[lsteffen]

Here is my worksheet.

Excel Workbook
	A	B
1	#VALUE!	BOU1407
2	#VALUE!	CH78990
Raw_Data

Excel 2003

Cell Formulas
Range	Formula
A1	=INDEX(B1, AcctNoLookup!A1:A22,0)
A2	=INDEX(B2, AcctNoLookup!A2:A23,0)

Excel Workbook
Name	Refers To
AcctNoLookup	=AcctNoLookup!$A$1:$A$22
Workbook Defined Names

 

[markmzz]

What you have in the sheet AcctNoLookup?

Post a small example.

Markmzz

 

[lsteffen]

AcctNoValue:

BOU
CD
ABC
XY
ZZT

 

[markmzz]

If I understand correctly what you want, then this can help you:

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">BOU</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">CD</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">ABC</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">XY</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">ZZT</td></tr></tbody></table><p style="width:7.2em;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">AcctNoLookup</p><br /><br /> <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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">BOU</td><td style=";">  BOU1407</td><td style="background-color: #FFFF00;;">BOU</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";"></td><td style=";">  CH78990</td><td style="background-color: #FFFF00;;">CH</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">CD</td><td style=";"> CD892</td><td style="background-color: #FFFF00;;">CD</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;"></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="background-color: #FFFF00;;">Helper Column</td></tr></tbody></table><p style="width:4.8em;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">Raw_Data</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">A1</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">MATCH(<font color="Green">C1,AcctNoLookup,0</font>)</font>),"",INDEX(<font color="Red">AcctNoLookup,MATCH(<font color="Green">C1,AcctNoLookup,0</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">B1,1,MIN(<font color="Green">FIND(<font color="Purple">{0;1;2;3;4;5;6;7;8;9},B1&"0123456789"</font>)-1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><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>Workbook Defined Names</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">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AcctNoLookup</th><td style="text-align:left">=AcctNoLookup!$A$1:$A$5</td></tr></tbody></table></td></tr></table><br />

If I'm wrong, give me more information.

Markmzz