Help with a lookup that looks for part of a string

**Ainsley** · 08-02-2012, 05:41 AM

Hi, Im going mad trying to figure out whats wrong with this forumla:

=VLOOKUP(MID(E5,3,2),L76:N77,3,FALSE)

E5 contains the number 4010805010
L76:N76 contains an array with the number 10 in L:76 and the word "Direct" in N:76

It should see E5 and start at the third character and return two characters = 10
Then vlookup this value (10) in the array and return the word "Direct"

So why does it give me a #N/A ?

If I remove the MID(E5,3,2) part and just write the number 10 it works fine. But I need it to use the number 10 in the long number 4010805010 to work.

**Pepe Le Mokko** · 08-02-2012, 05:44 AM

Perhaps

Please Login or Register  to view this content.

(MID returns a string and your lookup values are probably numbers)

**npamcpp** · 08-02-2012, 05:49 AM

The result of the MID() function is a text value -- "10". The cell L76 has a number -- 10
These two are not the same, even if they look the same.

If you want to look up a number, you need to be sure to have a number as the search criterion, and not text that looks like a number.

try

=VLOOKUP(MID(E5,3,2)+0,L76:N77,3,FALSE)

The "+0" will coerce the text value into a numeric value and your Vlookup will show the desired result.

---------- Post added at 09:49 PM ---------- Previous post was at 09:48 PM ----------

sorry, PepeLeMoko, did not refresh before posting.