Help with VLOOKUP()

[Guest]

I'm trying to engage

=VLOOKUP(C202,Grouping!$D$7:$E$377,2,FALSE)

In sheet2 where my cell C202 is 230000 (a number)
and in Column D on my Grouping Sheet I have a row
(actually row #16 or Cell D16 is also 230000 (a number)

My E16 has in it '230 This is what I need (as Text) But I'm getting
the #N/A result.

WHY?

 

[RagDyeR]

C202 and D16 *Do Not Match*!

They may look the same but *something* is different.

Are either of these cells the results of calculations, where maybe your
formatting is masking the *true* cell value?
Are either of the cells imported, where there may be invisible characters
present?
Are both of these cells *true* XL recognizes numbers?

Manually type in the same value in *both* cells, and then post back the
results.
--

HTH,

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

I'm trying to engage

=VLOOKUP(C202,Grouping!$D$7:$E$377,2,FALSE)

In sheet2 where my cell C202 is 230000 (a number)
and in Column D on my Grouping Sheet I have a row
(actually row #16 or Cell D16 is also 230000 (a number)

My E16 has in it '230 This is what I need (as Text) But I'm getting
the #N/A result.

WHY?

 

[Bob Flanagan]

Are you certain both 230000 entries are numbers? Make the columns real wide
and do an edit clear formats. If one is left justified, it is a text entry,
not a number. To solve, type a 1 in another cell, copy it, then highlight
the text entries and do a edit, paste special, multiply. This will convert
the text numbers to numbers. Make a backup first just in case!

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Guest]

RagDyeR;
You're right - some of these number are imported or else
the D column was pre-formatted as Text - Thanks for the help..

Jim

 

[Ragdyer]

Appreciate the feed-back.

 

[JMay]

Thanks Bob..
I spent an afternoon yesterday in IsitTextOrIsitANumber HELL.
Setting up a recurring template utilizing the VLOOKUP() function
I import A field from a Bank Download entitled Location Number.
It can take different forms depending on various records we get
from the bank -- The best record shows this field as a constant
10 digit number such as 0002300000 << and it naturally in it's
context a text field (will never be used in any calculation).

Yet when I download the Statement Off the web in this case it
will come into my spreadsheet as 2300000. What I really need to do
is strip the whole field down to a more practical reference, in this
case 230 -- So there is where I am trying to convert (using
the Vlookup function to Lookup the 2300000 in the master table where
I map everything out and show a column for the 0002300000, also the
2300000 with a final column as 230.

Without doing a full scale test of a cell (using either ISTEXT() or
ISNUMBER() - Visually I can't tell what I've got, since just because
it is left justified doesn't mean it's text (it could well be a number)
Then there's this thing where you can select a number - go to Format,
Cells, Text <<< Does this Format feature make the underlying content
TEXT, or does it only Format it Just to make it look like text?

I ended up utilizing the ' (apostrophe) several places to get things
settled down - Although I know that next month when I import my data
is going to once again come in as numerics - I set up a macro where
I can Highlight these guys and run this Macro

Sub AddApostrophe()
For Each c In Selection
c.Value = "'" & c.Value
Next c
End Sub

Not sure this is what is need but FOR NOW it seems to be
A WAY OUT (of my Trouble)...

Thanks,

Jim May

 

[Ragdyer]

Say your C202 is a number keyed in by you ... 230.

Try this variation to your Lookup() formula:

=VLOOKUP("*"&C202&"*",Grouping!$D$7:$E$377,2,0)

 

[JMay]

hummmmm;;; very interesting --I'll give it a shot.
thanks,
Jim