Way to Incorporate Trim Into Match/Index Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paige - 20 Nov 2006 22:35 GMT

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)))

Reply to this Message

Fred Smith - 20 Nov 2006 23:08 GMT

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)))

Signature

Regards,
Fred

>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
[quoted text clipped - 3 lines]
>
> =IF(ISNA(MATCH(E2,V:V,0)),"",INDEX(AB:AB,MATCH(E2,V:V,0)))

Reply to this Message

Paige - 20 Nov 2006 23:18 GMT

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.

> Certainly. Just surround E2 with Trim(), as in:
>
[quoted text clipped - 7 lines]
> >
> > =IF(ISNA(MATCH(E2,V:V,0)),"",INDEX(AB:AB,MATCH(E2,V:V,0)))

Reply to this Message

Teethless mama - 20 Nov 2006 23:51 GMT

Did you do "ctrl+shift+enter" after you enter the formula?

> 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.
[quoted text clipped - 10 lines]
> > >
> > > =IF(ISNA(MATCH(E2,V:V,0)),"",INDEX(AB:AB,MATCH(E2,V:V,0)))

Reply to this Message

daddylonglegs - 21 Nov 2006 00:05 GMT

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.....

Reply to this Message

Paige - 21 Nov 2006 00:16 GMT

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.

> 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.
[quoted text clipped - 20 lines]
> > > >
> > > > =IF(ISNA(MATCH(E2,V:V,0)),"",INDEX(AB:AB,MATCH(E2,V:V,0)))

Reply to this Message

Teethless mama - 20 Nov 2006 23:10 GMT

=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 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
[quoted text clipped - 3 lines]
>
> =IF(ISNA(MATCH(E2,V:V,0)),"",INDEX(AB:AB,MATCH(E2,V:V,0)))

Reply to this Message