look up formula not returning data

H

Hopeful

I have been working with vlookup tables for a while now but when I started a
new one with some new date from another source, it stopped working. It will
not return values that I ask it to lookup. The date is both numerical and
text. I have sorted the date in ascending order. I have tried changing the
data from number to text to general. Nothing seems to work.

If I open another sheet up and try to a vlookup it works. It is just not
working with my new data.

Thanks
 
R

Richard Buttrey

I have been working with vlookup tables for a while now but when I started a
new one with some new date from another source, it stopped working. It will
not return values that I ask it to lookup. The date is both numerical and
text. I have sorted the date in ascending order. I have tried changing the
data from number to text to general. Nothing seems to work.

If I open another sheet up and try to a vlookup it works. It is just not
working with my new data.

Thanks
Click to expand...

What's the formula you're using, and are you sure the subject of the
vlookup i.e. the first term, really exists in your data?

What error message are you getting?

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
D

Dave Peterson

Changing the format of the cell isn't enough to change the value in the cell.

If you want to convert a bunch of cells from Text numbers to number numbers,

you can select an empty cell
then edit|Copy
select the range to fix
edit|paste special|check add
 
P

Pete_UK

If you have pasted data in from another source, it may look like
numbers to you but could in reality be text.

Also, if you are looking up text values, then you may find that there
are leading or trailing spaces, so the data value may "appear" to be
the same as the lookup value but is actually quite different.

Hope this helps.

Pete
 
B

Boo

Hopeful,

This sounds like a formatting issue (which probably won't come as a surprise
to you!).

For example, if column A contains the values 1,2,A,B and the corresponing
entries in column B are One, Two, AAA, BBB a lookup formula of

=vlookup("1",A1:B4,2,false) will return #N/A because Excel is looking for
the text value 1 and not the numerical value 1 (which sounds like the problem
you are experiencing).

If I change the format of column A to be text this should solve the problem
- no! The formula will not update unless you edit the cell (F2) and then
press return - you don't want to do this for all cells!

Best way to get round this is choose a column with no data, enter a formula
along the lines of =proper(A1), copy down so all values you will be looking
up on are covered. Select this list of formulas, copy and then paste special
the values back to your original column.

Excel will now pick up the lookup values for you (assuming there is a match).

Sounds long winded but it takes a few seconds to do.
 
G

Gord Dibben

Sounds like the data format is not what you think it is.

If you think a cell is a number try this to validate =ISNUMBER(cellref) TRUE
or FALSE is returned.

If a number is formatted as text, simply re-formatting will not change it.

You must force a change to make it a real number.

Format all to General.

Copy an empty cell and select your data range.

Edit>Paste Special>Add>OK>Esc

If that doesn't work, maybe you have some extra spaces in the data.

Try running =TRIM(cellref) to remove.


Gord Dibben MS Excel MVP
 
Top