Formatting a cell: avoiding f2 & enter combination

Should be easy to fix: I'm doing a vlookup off two separate sheets that works totally fine when I get the formatting on both databases the same and after i hit "f2" and then the "enter" key. i have to do this for over 16K cells (rows) and it is laborious. is there a simple formula/cell conditioning excercise to make both sheets talk with each other?

[IML]

On 2002-02-28 11:36, Anonymous wrote:
Should be easy to fix: I'm doing a vlookup off two separate sheets that works totally fine when I get the formatting on both databases the same and after i hit "f2" and then the "enter" key. i have to do this for over 16K cells (rows) and it is laborious. is there a simple formula/cell conditioning excercise to make both sheets talk with each other?

If you are mixing numbers and text numbers that mix-match, you could either do a quick text to columns over both keys or edit your formala to something like
=vlookup(a1+0,table,2,0)
or
=vlookup(text(a1,"00"),table,2,0)

good luck

[ This Message was edited by: IML on 2002-02-28 11:43 ]

Thanks for the tip, but that isn't exactly my problem. I should have been more clear. I have two sheet of zip codes that i am trying to vlookup off of and even though the two sheets are in the same format, the vlookup doesn't recognize them. only after i hit "f2" and then "enter" am i able to get the desired result. HELP! I have over 16K zip codes that I need to query off of in the vlookup.

[IML]

Sorry if I misunderstood. A quick way to verify this is not the cause of your problem is to put to use the =isnumber() formula on each file. If one it returning true, and the other false, this would be the cause of your problem.

On 2002-02-28 11:49, Anonymous wrote:
Thanks for the tip, but that isn't exactly my problem. I should have been more clear. I have two sheet of zip codes that i am trying to vlookup off of and even though the two sheets are in the same format, the vlookup doesn't recognize them. only after i hit "f2" and then "enter" am i able to get the desired result. HELP! I have over 16K zip codes that I need to query off of in the vlookup.

You are exactly correct. when I ran "isnumber", those that match are true and those that don't are false. Now that I have identified cells which are recognized as numbers and those that aren't, how do i make them all consistent (into numbers)?

[IML]

On 2002-02-28 13:18, Anonymous wrote:
You are exactly correct. when I ran "isnumber", those that match are true and those that don't are false. Now that I have identified cells which are recognized as numbers and those that aren't, how do i make them all consistent (into numbers)?

If it is static data, the easiest thing to do would be to turn your text numbers into real numbers by either
1) Selecting the data, select text to columns from the data menu, and hit finish
or
2) Put "1" in an unused cell. Copy it. Highlight the text numbers and paste speical multiply.

If the data is dynamic, play with two formula aboves to find the one will match the two data types correctly good luck