Way to Incorporate Trim Into Match/Index Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following formula and am trying to incorporate the trim function
into it; i.e., I want to match the trimmed E2 to the trimmed V:V. Is there a
way to modify the formula to do this; haven't been successful yet in figuring
this out. Just adding trim in (or clean) doesn't work, at least not how I've
tried it so far.

=IF(ISNA(MATCH(E2,V:V,0)),"",INDEX(AB:AB,MATCH(E2,V:V,0)))
 
Certainly. Just surround E2 with Trim(), as in:

=IF(ISNA(MATCH(TRIM(E2),V:V,0)),"",INDEX(AB:AB,MATCH(TRIM(E2),V:V,0)))
 
=IF(ISNA(MATCH(TRIM(E2),V:V,0)),"",INDEX(AB:AB,MATCH(TRIM(E2),TRIM(V:V),0)))

ctrl+shift+enter (not just enter)
 
I have tried both of these and they don't seem to work; they don't find a
match even tho I know there should be one.
 
If you need to trim the cells in column V then you can't refer to the whole
column, pick a specific range, i.e.

=IF(ISNA(MATCH(TRIM(E2),TRIM(V1:V100),0)),"",INDEX(AB1:AB100,MATCH(TRIM(E2),TRIM(V1:V100),0)))

confirmed with CTRL+SHIFT+ENTER

......but might be better in the long run to try to trim your data so you
don't need such complex formulas.....
 
Once I changed it to a specific range and did Ctrl Shift Enter, worked like a
charm! Thanks so much to all of you. Have a great evening.
 
Back
Top