Vlookup

[donyc]

Hi all, have a small question..

I am using VLOOKUP to locate data for a list of numbers. however my numbers are preceded by aposthropes. My VLOOKUP will only work if I click the little exclamation mark box and format as number. Is there any other way to vlookup avoiding the the formating?

When i created a macro to format my numbers it seems my macro does not pick that up?

Has anyone else experienced this?

[Richard Buttrey]

Hi,

It sounds like you're looking up a number in a range which contains text values.Try converting the first term of the VLOOKUP(), currently presumably a number, to text.
i.e.

Please Login or Register  to view this content.

HTH

[donyc]

I get an error "#NAME?" by A1 I pressume you mean cell where my number is?

[Richard Buttrey]

Hi,

Yes, that's correct.

Rgds

[donyc]

it actually just gives me same error #N/A.. it is really strange..

When I click the exclamation mark and choose "Conver to Number" my vlookup works

When I right click, go to Format Cells, and Choose> Number it does not work...

Any suggestions>?

[peterjuhnke]

Hello
Have you converted them all to numbers? to do this, place a 1 in any empty cell, make sure it is stored as a number, copy it, highlight (select) the data you want to convert, special paste, multiply.
Regards
Peter

[Richard Buttrey]

Hi,

Before it was a #NAME error so something has changed.

I suggest you upload the workbook so that we can see the question in context.

Rgds

[jmff]

The Richard's solution must work!

Maybe you forgot to replace the "myrange" argument in the sample formula provided: =VLOOKUP(TEXT(A1,0),myrange,2,FALSE)

You may have written something like this: =VLOOKUP(TEXT(A1,"0"),myrange,2,FALSE)

Excel does not find the named range "myrange" and returns the error "#NAME?"

Regarding the cell formatting, it does not work because of the apostrophe (Excel cannot get rid of it by simply change the format of a cell).

I recommend you to make the Vlookup formula more coherent, if you are looking up numbers then the lookup column must contain numbers.

In your case, it is very simply and it takes no time to do it...

To convert all the lookup columns into numbers without clicking on the exclamation mark cell by cell do this...

Select the entire column and then select the exclamation mark and click convert to number. All the cells will be converted to numbers (the apostrophe will disappear)