vlookup with numbers

[Phil Hibbs]

Why does doing a vlookup of numbers against text have to be so damn
hard? I eventually got around it by creating a new column with a
formula =""&A1 and used that as the search value in the vlookup. Is
there not a way of just making it work? It compares them as equal, a
COUNTIF counts them just fine, but VLOOKUP doesn't find it. Very
frustrating!

Phil.

 

[Don Guillett Excel MVP]

Why does doing a vlookup of numbers against text have to be so damn
hard? I eventually got around it by creating a new column with a
formula =""&A1 and used that as the search value in the vlookup. Is
there not a way of just making it work? It compares them as equal, a
COUNTIF counts them just fine, but VLOOKUP doesn't find it. Very
frustrating!

Phil.

Quite possibliy a matter of proper formatting?
"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Phil Hibbs]

Quite possibliy a matter of proper formatting?

Formatting never seems to make a difference to vlookup. I tried
formatting the original cell as Text, made no difference. I tried Copy
and Paste Values, no difference. I tried editing the cell and putting
an apostrophe at the beginning, that worked! But I couldn't face
editing all 1545 values, so that was clearly not an option. Making a
copy column with the =""&A1 formula was by far the easiest solution -
and it didn't matter if this cell was formatted as number or general
or text, the vlookup and countif both worked.

Phil.

 

[Phil Hibbs]

Oh, and all this with my boss breathing down my neck saying "That's
too complex. Make it simpler", because I have to document each step to
hand over to someone with less Excel experience. "I can't make it
simpler! Excel is just being damn awkward!"

Phil.

 

[Phil Hibbs]

I just did a couple of test cases. Excel will not find a number in a
list of text values, or a text value in a list of numbers. OOo will
find a text value in a list of numbers, but not a number in a list of
text values. It is consistent between COUNTIF and VLOOKUP, but in
Excel, COUNTIF works in all cases.

Phil.

 

[Max]

For possibly better results with data as-is, you could try this kind
of index/match alternative which swings it all to pure text matching,
normal ENTER to confirm:
In B2: =INDEX(x!$B$2:$B$100,MATCH(TRUE,INDEX(x!$A$2:$A$100&""=A2&"",),
0))
where A2 = lookup value, to be matched within x!$A$2:$A$100,
with the desired returns (where it matches) from x!$B$2:$B$100
Concatenating "" to both A2 and x!$A$2:$A$100 makes it all pure text,
enabling consistency in matching

 

[Don Guillett Excel MVP]

Formatting never seems to make a difference to vlookup. I tried
formatting the original cell as Text, made no difference. I tried Copy
and Paste Values, no difference. I tried editing the cell and putting
an apostrophe at the beginning, that worked! But I couldn't face
editing all 1545 values, so that was clearly not an option. Making a
copy column with the =""&A1 formula was by far the easiest solution -
and it didn't matter if this cell was formatted as number or general
or text, the vlookup and countif both worked.

Phil.

Not a good solution.

 

[Phil Hibbs]

Not a good solution.

....but the only one that worked. Apart from editing them all and
putting an apostrophe in front. I don't think there is a "good"
solution - but I'll try Max's "match" suggestion when I get a spare
minute.

Phil.

 

[joeu2004]

Don Guillett wrote:
I couldn't face
editing all 1545 values, so that was clearly not an option. Making a
copy column with the =""&A1 formula was by far the easiest solution -
and it didn't matter if this cell was formatted as number or general
or text, the vlookup and countif both worked.

So instead of a helper column, perhaps you could have written the
VLOOKUP as:

VLOOKUP(""&A1,...)

Alternatively, perhaps the following would have worked:

VLOOKUP(--A1,...)

You really have not provided enough details for us to offer more
constructive suggestions, if those do not work.

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

 

[Cartman]

Phil, I sympathize. Did you import the data from a webpage? Are the
numbers visually left justified under general unjustified display
format?

/RantAgainstTheUnlisteningMachine on: I believe others agree that
there is a serious deficiency in Excel in not clearly delineating
internal representations for the user. Without going into properties
(maybe in VBA) it is virtually impossible to distinguish exactly what
a representation is (and I don't know if there is a magically
informative property - tell me if you know). Further complicating it
is the numerous kinds of "text." The thing under format? Single
apostrophe? Value-pasting? It is unforgivable that one can't hover or
hit format or right-click for properties and explicitly see the
difference between "this kind of text" vs. "that kind." Microsoft
needs to acknowledge that there is zero-length-string(ZLS) vs. Empty
vs. Nothing vs. Missing vs. Null vs. "" vs.Used (and vs. how many
others I don't immediately recall), and report what the software knows
to the user. Why do we have to WASTE TIME determining these?

Another example of different kinds of "text" : sometimes, when you
have a row of cells, all "seemingly" empty, LEN of zero (you can hit
the delete key on each of them to assert that)...the thing is,
sometimes you can end-right and it stops at the end of them. Other
times you go end-right and you end up at column IV. (And I'm not a
lowly beginner talking about neglecting invisible blanks (0X20) - the
LENs are 0. That's the kind of level 1 support answer typically
provided. Someone should do better.)

With ActiveSheet.UsedRange
..Value = .Value
End With
Why does the above do anything? Just what does it do? It is, perhaps
by malicious design, left to be a "mystery" to the user. No
"hovering". They've known about this for at least 20 years. (Biting
tongue about incompetence)

To take a whine break - though the whines do deserve attention and
action - here are some resources to understand empty state, etc.
Additions are welcome.
http://www.experts-exchange.com/Sof...Null-Empty-Blank-N-A-ZLS-Nothing-Missing.html
http://allenbrowne.com/vba-NothingEmpty.html

It's just that MS has ceased to support the Usenet newsgroups.

WTF? Tough economic times at one of the (if not "the") world's richest
companies? It wasn't broke, so they're fixing it.

Phil, sorry to invade your thread to rant about MS's cluelessness
about the user. I do believe that understanding the different internal
representations as I've raised may be enlightening to taking command
of your current dilemma. I am sympathetic - I've been there and wasted
substantial time with your very issue - and sorry I don't have a catch-
all reply. You have a strikingly generous offer by [highly qualified]
Don to look at it specifically.

 

[Don Guillett Excel MVP]

...but the only one that worked. Apart from editing them all and
putting an apostrophe in front. I don't think there is a "good"
solution - but I'll try Max's "match" suggestion when I get a spare
minute.

Phil.

I repeat my offer to look at your file