Using Trim() in the Second Parameter of a Vlookup

I pretty much live in IFFERROR(VLOOKUP(..... I have run in toan issue, every once in while, where the table array data needs to be TRIM()'d. I have used the TRIM() in the first parameter of the VLOOKUP with great success but I can't seem to figure out where to put the TRIM() function for the second parameter(table array), if in fact, you can. I do realize that I can go the area that needs to be TRIM()'d and "fix it" before I do the VLOOKUP but I would prefer to do it all in one formula.

=IFERROR(VLOOKUP(A1,Sheet2!A:D,2,),0)

so the Sheet2!A:D data in the Col A needs to be TRIM()'d, but, again, I can't seem to figure out the syntax.

Thanks in Advance

Hi judgeh59,

Can you upload a sample workbook if you need a workaround on this... thx


Regards,
DILIPandey
<click on below * if this helps>

Here is the upload....

Hercules - I can "Clean up the data" prior to doing the vlookup....are you talking about doing a TRIM() in the same formula string?....because I'm not sure how to do that....

Hi,

Why you not use this

Please Login or Register  to view this content.

and copy down on empty column and copy value back the result on column A?
It's more easy than you think a complex formula for to do that.

Okay.. use below formula:-

Formula:

Please Login or Register  to view this content.

enter this with key combination: ctrl shift enter

see attached:- ExcelForumUpload.xlsx

Regards,
DILIPandey
<click on below * if this helps>

SDCH - mostly because I never know if the data I'm getting needs to be trimmed and I don't really want to be switching between sheets if I don't have to....I totally understand what you are saying - was hoping for a simple solution....

DILIPandey - I never even considered an array formula - looks like it works perfectly....thank you for your help and have a star....

thanks to everybody who had suggested stuff...

you are welcome judgeh59.. glad to help


Please mark this thread as [SOLVED].. thx

Regards,
DILIPandey
<click on below * if this helps>

DILIPandey - I have marked this as solved, but I don't see any place that shows I gave you a star....am I missing something?

I
got it.. so if you give 'star' to any user that can be seen by that user in his /her control panel:-

http://www.excelforum.com/usercp.php


Regards,
DILIPandey
<click on below * if this helps>

thank you Sir

you are welcome sir


Regards,
DILIPandey
<click on below * if this helps>

Hercules - and I have done that in the past because I needed to...and I have recommended that in past also....I was just looking for something I could build in to the formula....the array formula works but does take a bit of a performance it because it generates the array table...I'll probably add the trim to my bag of vlookup tricks but probably recommend to certain user to do it your way (and mine in the past)....thanks for helping and the suggestions....