Way to Incorporate Trim Into Match/Index Formula
 

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

Way to Incorporate Trim Into Match/Index Formula
 

Certainly. Just surround E2 with Trim(), as in:

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

--
Regards,
Fred


"Paige" wrote in message
...
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)))

Way to Incorporate Trim Into Match/Index Formula
 

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

ctrl+shift+enter (not just enter)



"Paige" wrote:

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

Way to Incorporate Trim Into Match/Index 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.

"Fred Smith" wrote:

Certainly. Just surround E2 with Trim(), as in:

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

--
Regards,
Fred


"Paige" wrote in message
...
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)))

Way to Incorporate Trim Into Match/Index Formula
 

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


"Paige" wrote:

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.

"Fred Smith" wrote:

Certainly. Just surround E2 with Trim(), as in:

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

--
Regards,
Fred


"Paige" wrote in message
...
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)))

Way to Incorporate Trim Into Match/Index Formula
 

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

"Paige" wrote:

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.

"Fred Smith" wrote:

Certainly. Just surround E2 with Trim(), as in:

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

--
Regards,
Fred


"Paige" wrote in message
...
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)))

Way to Incorporate Trim Into Match/Index Formula
 

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.

"daddylonglegs" wrote:

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

"Paige" wrote:

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.

"Fred Smith" wrote:

Certainly. Just surround E2 with Trim(), as in:

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

--
Regards,
Fred


"Paige" wrote in message
...
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)))