Vlookup - exclude a blank cell

[amanda]

Hi,

I am trying to use a VLOOKUP on a list of locations. In one list I have the
name, in the other it is the name with a space at the end. For example:

List one: SOUTHAMPTON
List two: SOUTHAMPTON_

List two is from a system so I cannot stop it putting a space at the end,
and it is this list that I need to VLOOKUP from.

How do I get the VLOOKUP to ignore the space on the end? (The locations are
various lengths and may have two words with a genuine space in the middle).

Hope this makes sense.

Thanks

Amanda

 

[Sam Wilson]

rather than

=VLOOKUP(A1,B1:C10,2,FALSE)

use

=VLOOKUP(TRIM(A1),B1:C10,2,FALSE)

etc

 

[wcp]

=VLOOKUP(TRIM(A1),B1:C10,2,FALSE) / means to ignore all blank from cell
lookup_value.
But , I think Amanda need to ignore blank in B1:C10 (table array). So can
she use trim at the table array and use to vlookup? :
List one: SOUTHAMPTON
List two: SOUTHAMPTON___
--> list three : trim(b1) --> result = SOUTHAMPTON (no blank already)
now she can use the list three to lookup.


"Sam Wilson" เขียน:

 

[Sam Wilson]

There's no need for an extra list if she does,

=VLOOKUP(A1,TRIM(B1:C10),2,FALSE) eneterd with Ctrl+Shift+Enter to make it
an array formula would work.

 

[Dave Peterson]

could you just append a space character in the value to match:

=vlookup(a1&" ",sheet2!a,b,2,false)

Personally, I'd take the time to clean up that system file. If I couldn't
change the original file, I'd change the copy that I opened (and not save when I
closed it!).