vlookup number formats (I think!)

I can get vlookup to do everything I want except for this: numbers in my 'lookup' column SEEM to me to match numbers in my 'reference' column but vlookup doesn't think they match. I've tried formatting either/both columns every way possible. I've put them in ascending order, I've formatted them as text, as numbers, as general...nothing seems to work. The numbers appear exactly the same and, yet, they're not a vlookup match. Can anyone help me?

well formats only change how a value is displayed not the value below. You need to round your numbers using the round function or in the tools options set decimal places to fixed at watever level you wish

Regards

Dav

Thanks but I've already tried your move-the-decimal suggestion. Your 'rounding' suggestion scares me because a lot of the numbers are very similar and I'm looking for an exact match. What's weird is that, even if I cut and paste a number from my 'look-up' column into my 'reference' column, vlookup doesn't recognize it as being the same. Wouldn't you think that cut-and-paste would create identical numbers in both columns?

Originally Posted by bbuffum

Thanks but I've already tried your move-the-decimal suggestion. Your 'rounding' suggestion scares me because a lot of the numbers are very similar and I'm looking for an exact match. What's weird is that, even if I cut and paste a number from my 'look-up' column into my 'reference' column, vlookup doesn't recognize it as being the same. Wouldn't you think that cut-and-paste would create identical numbers in both columns?

That computers have no concept of ten cents is the scary point about matching numbers.
Assuming that the lookup table has the correct value, and the calculated-value-you-are-looking-up is the miscreant, try something like

=VLOOKUP(VALUE(TEXT(A1,"#.00")),table,1,FALSE)

---

Both of the numbers (the one in the reference column and the one in the look-up column) appear EXACTLY the same. Vlookup doesn't even work when I cut and paste a number from one column to the other. Wouldn't that make them EXACTLY the same and cause Vlookup to return a value?

Originally Posted by bbuffum

Both of the numbers (the one in the reference column and the one in the look-up column) appear EXACTLY the same. Vlookup doesn't even work when I cut and paste a number from one column to the other. Wouldn't that make them EXACTLY the same and cause Vlookup to return a value?

No, especially if $0.10 is involved.

Did my test work for you?


If not, can you cut just the error rows to a sheet and post here?

---

Ur numbers may be matching, but their formats must be different. When you change text to a number format using Format--Cells option, u donot necessarily change text to number. Just check whether ur numbers r actually numbers. Put the formula =ISNUMBER(Cell Ref) This will show false if those are not numbers but text. Then convert those numbers to actual numbers by using this formula =VALUE(Cell Ref) After having converted all those numbers apply VLOOKUP function.