using VLOOKUP with extracted text

[anc]

I am trying to use the VLOOKUP function to look up values using text that is are extracted from a longer string (containg both text and numbers). I only want to look up the text part of the string as the numbers are variable over time. The info is always updated in that format from another data source.

For example, I want to VLOOKUP 'Sales' where I have extracted 'Sales' from 'Sales (March 2001)' using the MID & FIND formula [=MID(A4,1,FIND(" (",A4,1)-1)]

Unfortunately VLOOKUP doesn't seem to be able to identify this is the text that is to be looked up and returns a false value #N/A.

Any suggestions?

 

[NBVC]

Does your vlookup look something like this then?

=VLOOKUP(MID(A4,1,FIND(" (",A4,1)-1),G3:H15,2,FALSE) where G3:H15 would be the lookup table range.


Make sure also that the string "Sales" in the lookup table has no spaces before or after.

 

[pgc01]

Hi anc
Welcomr to the board

You can use wildcards with VLOOKUP

Try

=VLOOKUP("*sales*",A1:B10,2,0)

Hope this helps
PGC

 

[anc]

I think that hidden spaces due to the downloaded format might have been causing the problem. The wildcard approach seems to get round this.

Many thanks to both replies.