vlookup

  • Thread starter Thread starter KP
  • Start date Start date
K

KP

HELP!

My lookup formula does not always work in the same
spreadsheet, but if I go to the lookup table spreadsheet,
find the matching number and hard enter that number, the
formula then works in the new spreadsheet, sometimes.

Also, if a number is working, and I hard enter the number
in the lookup table (just to check) the formula then does
NOT work in the target spreadsheet, sometimes.

All formats in both spreadsheets are numeric.

Is this a formatting problem, or do I need to contact the
person who sent me the lookup table spreadsheet. It may
be imported from another system.

Thanks for any suggestions. This is making us nuts!!!
 
Hi
probably though you have formated the cells as numbers the values are
stored as Text values. Try the following:
- select an empty cell and copy it
- select your lookup range
- goto 'Edit - Paste Special' and choose 'Add'

this should convert all numieric values to real numbers
 
Hello back!

Sorry. I tried this with a blank cell in both the
original worksheet and a new blank spreadsheet and I still
have that same problem.

Thanks for your help.
 
Hi
I assume that the lookup value does NOT match anything in your lookup
range. You may test this manually. enter the formula
=cell_with_lookup_value=cell_which_should_match

This should return TRUE. If this does return FALSE the values are NOT
idetical. e.g. contain spaces, invisible characters, etc.
 
KP

Could be some of the "numbers" are text. Simply Re-formatting to Number or
General will not work.

Format all cells as Number or General then.........

Copy an empty cell which is formatted as Number or General. Select your other
cells and Paste Special>Add>OK>Esc.

This should change them to real numerics.

Gord Dibben Excel MVP
 
Good Morning,

The lookup value DOES match items in the lookup range.
That is what is so frustrating. If I hard enter the
number in the lookup range over itself, the formula then
does work.

Makes you nuts, huh?

Thanks so much for your help. I am going to go to the
person who sends me the spreadsheet to be sure she does
not get this information from another system that may be
sending this as text not numeric. Hopefully we can fix
this this way.

Thank you 'o Excel guru!
 
Back
Top