V-Lookup for numbers in various formats

**rena** · 08-15-2007, 02:14 PM

I assumed that V-Lookup recognizes numbers in whichever format they are. This does not seem to be the case, however. Formatting the columns of a variety of entries (text, number) doesn't work. Although after formatting the column to number and the format APPEARS to be number, it is not. And V-Lookup still won't pick it up. Anyone know what a solution to this might be?

**Ron Coderre** · 08-15-2007, 02:27 PM

Hi, rena

You'll need to give an example of what you're experiencing.
VLOOKUP is properly differentiating numeric text from numbers in Excel 2002 and 2003.

Example:

Please Login or Register  to view this content.

If D1: 1
E1: =VLOOKUP(D1,A1:B8,2,0)
Formula returns x

If D1: '1
E1: =VLOOKUP(D1,A1:B8,2,0)
Formula returns g

However, you might be running into this problem.....
If you change a number's format to Text
<format><cells><number tab>....Category: Text

....The number will 'look like' text, but won't actually convert until you edit the cell and press [enter]
(If you have a lot of these, I can show you a shortcut to editing all the cells)

Does that help?

**rena** · 08-15-2007, 02:40 PM

Surprise, that was a quick response

.

Actually the only reason I have all these various formats is because many people make entries in the spreadsheet via copy/paste (from a mainframe program) and I can never be certain what the result of the copy/paste is (text? number?). What I started to do is format the initial entries (which I will compare to later) into number and then do the same thing for the new entries. Don't know whether it will work. I have two v-lookups (for duplicate entries) for the same items (invoice #, waybill#) and this is how I realized that the v-lookup didn't give me accurate results (the invoice # showed up but not the waybill number). As you surmised, there are quite a few entries and to do the F2 thing would take some time (would that give me text or number?). But if you know of a quick way to do this, I would appreciate knowing about this regardless as I very often have to convert large numbers (+s) to show the actual number.

**Ron Coderre** · 08-15-2007, 02:53 PM

Here you go....

If you want all of the values to be numeric:

Select the range of values
Set the number format to an appropriate Number Format

Then (from the Excel Main Menu):
<data><text-to-columns>....Click [Next] until Step_3_of_3
Check: General...................Click [Finish]

OR....if you want them all to be Text
Set the format to Text
In text-to-columns Step_3_of_3.....Check: Text

That should do it.....Does it?

**rena** · 08-16-2007, 04:15 PM

Hi again,

I haven't tried your suggestion yet but I will (I remember using data/text-to-columns before for Access) but I wasn't sure what it actually did. I assumed it formatted the columns in a certain way. For now I'm starting out formatting the columns in question as numbers (hoping they actually "convert" if they're not). I'm not sure if I will continue this as I also have zeros in front of numbers - easier to add text formatted than number formatted.

But for your idea, I will have to create a test to see how and if it works. Will let you know when I've actually done it.

Rena