VLOOKUP not working

[Guest]

I’m having a problem with a VLOOKUP formula that’s not working and I could
use some help. Here’s an example of my problem.

File 1:
A1 (name): Bob Weir
B1 (sales): $100,000

File 2:
A1 (name): Bob Weir
B1(state): California
C1(vlookup to File 1): I’m trying to bring in the $100,000 from File 1 for
Bob Weir.

Here’s the troubleshooting I’ve done so far:
1.I’ve used the =Len formula to make sure that there are no spaces after the
name and each cell has the same number of characters.
2.I’ve tried to use both the General and Text format for both A1 cells and
the VLOOKUP doesn’t work with either format.
3.When I take cell A1 and Copy -> Paste Special -> Values cell A1 from File
1 to cell A1 of File 2, then my VLOOKUP works. Unfortunately, this isn’t a
solution because I have a lot of rows and the names are not in the same order
in the two files.

From the troubleshooting I’ve done, it looks the values aren’t really equal
in the various A1 cells but they appear identical. Any thoughts on how to fix
my VLOOKUP? Thanks in advance for your help.

-Scott

 

[JulieD]

Hi Scott

would have helped if you posted your actual formula too
however, i'm guessing you're only using 3 of the 4 parameters .. the 4th,
when omitted defaults to TRUE which means that you want an APPROXIMATE match
.... to get an EXACT match include either FALSE or 0 as the fourth parameter
=VLOOKUP(lookup_value, table_array, col_index_num,FALSE)

Cheers
JulieD

 

[Frank Kabel]

Hi
you may post your used formula

 

[RagDyer]

Since you say that your formula works when you copy and paste the lookup
value between files, then there's probably nothing wrong with the formula.
You also checked the length of the cells for any leading or trailing
invisible characters and found them to be absent.

So, the only thing that I can think of is ... what's *between* the names?
I'd say that there's a good possibility that you *don't* have a normal
<Space CHAR(32)> in between the names, but some other character, maybe a
CHAR(160), non-breaking space.

Type
bob weir
in A1
In B1 enter this:
=CODE(MID(A1,4,1))

You should get a return of 32.

Use this same formula on one of your cells that's giving you a problem and
see what return you get.
Make sure that the second argument of the MID() function matches the "space"
placement of the cell you're testing.

If it comes back with something other then 32, you can use that information
to <Edit> <Replace> the "bad" character *out* of the column that you're
using for a lookup value.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



I’m having a problem with a VLOOKUP formula that’s not working and I could
use some help. Here’s an example of my problem.

File 1:
A1 (name): Bob Weir
B1 (sales): $100,000

File 2:
A1 (name): Bob Weir
B1(state): California
C1(vlookup to File 1): I’m trying to bring in the $100,000 from File 1 for
Bob Weir.

Here’s the troubleshooting I’ve done so far:
1.I’ve used the =Len formula to make sure that there are no spaces after the
name and each cell has the same number of characters.
2.I’ve tried to use both the General and Text format for both A1 cells and
the VLOOKUP doesn’t work with either format.
3.When I take cell A1 and Copy -> Paste Special -> Values cell A1 from File
1 to cell A1 of File 2, then my VLOOKUP works. Unfortunately, this isn’t a
solution because I have a lot of rows and the names are not in the same
order
in the two files.

From the troubleshooting I’ve done, it looks the values aren’t really equal
in the various A1 cells but they appear identical. Any thoughts on how to
fix
my VLOOKUP? Thanks in advance for your help.

-Scott