Trim Range in Vlookup?

[beckys]

I have a very simple formula to do a vlookup:

=Vlookup(E2, MSP!C:I, 7, false)

Column C of the MSP tab has stock symbols - however, the source file that this tab gets populated with has spaces after the symbols, and they do not match the data in E2.

For example, column C of the MSP tab will show "MSFT "
while cell E2 would show "MSFT".

Is there any way to trim column C of the MSP tab within the formula or will we have to continue to do this as a separate step?

Thanks!
Becky

 

[jim may]

Give this a try... (What is the result?)

=Vlookup(E2 & " ", MSP!C:I, 7, false)

 

[Rasm]

Use

Trim(nameOfString) or Ltrim(nameOfString) or Rtrim(nameOfString)

 

[barry houdini]

Hello Becky,

You can use TRIM on the table array ....but that will make it an "array formula". It's best to restrict the range so try something like

=VLOOKUP(E2,TRIM(MSP!C$2:I$100),7,FALSE)

confirmed with CTRL+SHIFT+ENTER

adjust range as required

Note that will also "trim" the returned value.....

A better approach might be to use a wildcard like this

=VLOOKUP(E2&" *",MSP!C$2:I$100,7,FALSE)

Note that assumes that your "space" is actually a space and not some other "non printing character"

 

[beckys]

Barry - your solution - the array formula - is the winner.

Thanks all!