vlookup

[tlindeman]

I need a vlookup that ignores spaces. For example in my sheet "ENTERPRISE KPI" in cell A5, I have "Total Stockholder's Equity" , I want to look that up on the sheet "11-02 Statements" cells A:B. The problem is in cell A, there is total stock holder's equity in a cells, however, there is 5 spaces before the T. THe sheet "11-02 Statements" I can not change. is there a way to do a lookup based on the text and not the exact cell contents? I know I probably did not explain that very well so please let me know if you have more questions.

Thank you
Tony

 

[KrisBu]

You could try using the trim formula within the lookup?

 

[kidwispa]

I need a vlookup that ignores spaces. For example in my sheet "ENTERPRISE KPI" in cell A5, I have "Total Stockholder's Equity" , I want to look that up on the sheet "11-02 Statements" cells A:B. The problem is in cell A, there is total stock holder's equity in a cells, however, there is 5 spaces before the T. THe sheet "11-02 Statements" I can not change. is there a way to do a lookup based on the text and not the exact cell contents? I know I probably did not explain that very well so please let me know if you have more questions.

Thank you
Tony

Hi Tony,

Do you mean that on one sheet you have " Total Stockholder's Equity" (ie with 5 spaces before) and on another you have "Total Stockholder's Equity"?

You could try using TRIM within your vlookup...

=VLOOKUP(TRIM(A5),etc....

If that doesn't make sense then post examples of what you are trying to achieve and i'll have a go...

 

[MrKowz]

You are going to need to do this with an array-entered index/match formula:

<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 /></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=";">     Total Stockholder's Equity</td><td style="text-align: right;;">153</td><td style="text-align: right;;"></td><td style=";">Value to Find</td><td style=";">Total Stockholder's Equity</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">     Stuff</td><td style="text-align: right;;">123</td><td style="text-align: right;;"></td><td style=";">Value Returned</td><td style="text-align: right;;">153</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>Array 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">B1:B2,MATCH(<font color="Red">E1,TRIM(<font color="Green">A1:A2</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[Jonmo1]

Is there ALWAYS 5 spaces before the text value in Column A?

If so you can do

=VLOOKUP("xxxxx"&A5,etc...)

The xxxxx is supposed to be 5 spaces, the board software trimmed it...

 

[tlindeman]

Thank you all for your help, I did not think of the trim function, that will work well.

Tony