using VLOOKUP with extracted text

anc

New Member
Joined
Oct 11, 2006
Messages
3
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?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi anc
Welcomr to the board

You can use wildcards with VLOOKUP

Try

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

Hope this helps
PGC
 

anc

New Member
Joined
Oct 11, 2006
Messages
3
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.
 

Forum statistics

Threads
1,136,354
Messages
5,675,291
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top