VLOOKUP

[slamtheman]

Quick question. I have to look up text strings to return a definition. For example:

I am reporting a paramter called say "Phone calls through CSR"

now I want to use VLOOKUP to find this in another table, and return the definition from that table.

However, it's in the table as " Phone Calls through CSR" (in other words, there are whitespaces, and possibly case mismatches)

With VLOOKUP, this is returning "n/A" unless the match is EXACT. My question is, is there anyway to either modify VLOOKUP, or use another function to quickly match my terms to their definition, even if there are slight descrepancies in the text strings. Thanks for your help in advance.

 

[msvec]

You'll need to clean up the strings you're looking up, and apply the same changes to the lookup column in your source. I find it helpfuld to replace all the spaces and make everything upper case with the following:

=UPPER(SUBSTITUTE(A1," ",""))

I don't know what other unusual circumstances you may have, but you can clearly replace anything else that may be causing problems in both places.

 

[slamtheman]

thanks! unfortunately, Im lazy and was just hoping there was something that could be done to avoid the 'cleaning up' process (we're talking ALOT of clean up). Oh well. Thanks anyways, appreciate the prompt response.

 

[Todd Bardoni]

You can use "=TRIM(YourRange)" to get rid of extra spaces and "=CLEAN(YourRange)" to get rid of non-printable characters such as hard-returns.

 

[NateO]

Howdy, you could use a macro to clean all of your constants, looping through them, or you can use an array formula, something like (d or e):

Book5
	A	B	C	D	E
1	BlaH	2	PhonecallsthroughCSR	4	4
2	PhoneCallsthroughCSR	4	Blah	2	2
Sheet1 (2)

Hope one of these possibilites helps.