INDEX MATCH LEFT for first 25 characters (all text)

[lawi227]

I conducted a survey and I am trying to return a value using index match. I am realizing that some of the text of the survey has been edited, so my index match isn't finding what it needs to. I thought I would have a better return rate if I instead of matching the whole cell, I would match just the first 25 characters.

I am attempting to use this formula:
=INDEX(Data!$B$4:$B$134,MATCH(LEFT(Calculation!$E5,25),Data!$B$4:$B$134,0))

It is not returning any matches.

I've tried variations like this:
=INDEX(Data!$B$4:$B$134,MATCH(LEFT(Calculation!$E9,25)*1,Data!$B$4:$B$134,0))

Still doesn't work.

Does anyone know how to use the left formula or is there a better way to try to match text and return a value?

 

[Eric W]

Try:

=INDEX(Data!$B$4:$B$134,MATCH(LEFT(Calculation!$E9,25)&"*",Data!$B$4:$B$134,0))

 

[wideboydixon]

Try:

=INDEX(Data!$B$4:$B$134,MATCH(LEFT(Calculation!$E9,25),LEFT(Data!$B$4:$B$134,25),0))

Entered using Ctrl+Shift+Enter (array formula)

WBD

Or what Eric says

 

[Scott T]

=INDEX(Data!$B$4:$B$134,MATCH(LEFT(Calculation!$E5,25),Data!$B$4:$B$134,0))

Does the data in column B match the first 25 characters in column E?

if you have "I like the mr excel fourm a lot." in column b and the first 25 characters in column E are "I like the mr excel fourm" you would not get a match. you would need to use the left function on column B