Excel 2003 VLOOKUP Failing 1

[Hakala]

Hi, Tek-Tippers!
For some reason, VLOOKUP in Excel 2003 is giving me #N/A when I can see the matching value right there next to it.

I have a list of File IDs which are numerical but have a General Format in Excel, since we aren't adding them together or anything else mathematical. Each one is tied to a currency amount.

Each month, I need to know if the currency amount has changed from last month, and which File IDs weren't on last month's list (new File IDs with currency).

So using something like this:
400071 75
400073 60
400075 22
400078 221


and matching it against this:
400071 105
400073 75
400075 33
400077 135
400078 221

I should get

400071 30
400073 15
400075 11
400077 #N/A (new File ID)
400078 0

It has worked for months. Suddenly, I'm getting #N/A on File IDs that exist in my list, and worked last month.

The VLOOKUP is standard: =VLOOKUP($B52,I:J,2,FALSE)
Column B is File ID from the newest month's list.
Columns I and J are File IDs and currency from last month.

If the File ID was in last month's list, the second column's value should be shown (the currency amount) and if not, #N/A should show, telling me the File ID didn't exist last month.

But it's not... it's telling me File IDs are new when I can see them in Column I with currency in Column J.

What should I look for to troubleshoot this? Thanks for any help you can give me.


Michelle Hakala

http://www.winebird.com/

 

[SkipVought]

#N/A! definitely means that it cannot find the lookup value in the first column of the lookup range.

It could be that you have NUMBERS in one list and TEXT in the other of vis versa.

You could have a trailing space.

You have 2 things to look for.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Hakala]

Thanks, SkipVought!
I see nothing.... but when I entered the cell in Column I to verify no trailing space, when I left the cell, the currency value popped up into the spot where #N/A was before.

Is it possible it "thinks" there's a space?

And is there a way to fix it so the data doesn't require me to touch every cell every month?

Michelle Hakala

http://www.winebird.com/

 

[SkipVought]

Chances are the values in in column I are TEXT and the values in column B are NUMBERS, although the appear to be identical.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Hakala]

Checking the format, they both say Text. Changing the format from Text to Text or General to Text doesn't seem to affect it. Is there a way to force a format change?

Michelle Hakala

http://www.winebird.com/

 

[SkipVought]

Changing a format does not really change ANYTHING.

The underlying value remains UNCHANGED.

When you edited that cell to check for a space and then hit ENTER or TAB to exit the cell, that action change the value from a STRING to a NUMBER.

The PROBLEM is with the NEW LIST that you are using for your lookup. How did that list get into your workbook?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Hakala]

It's a copy/paste from last month's Column B... and last month it worked. That would have been a copy of the month before last's Column B.

It's very odd.

Michelle Hakala

http://www.winebird.com/

 

[SkipVought]

Sub ConvertNum2Txt()
'select the first cell in the column you need to convert

  Dim r as range
  for each r in range(Activecell, Activecell.end(xldown))
    if isnumeric(r.value) then r.value = "'" & r.value
  next
End Sub

Paste this in a MODULE. Run from the macro window.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Hakala]

LOL!

The ones that were failing are not failing now and the ones that weren't failing are.

I did something else, too, in a different workbook copy. Since our conversation made me wonder about *next* month's work I copied Column B into Column I. No issues.

Looks like it's only going to be a this-month problem.

Michelle Hakala

http://www.winebird.com/

 

[byundt]

It appears from your most recent comment that you need numbers rather than text that looks like numbers.

To convert data from text that looks like numbers into real numbers:
1) Copy a blank cell
2) Select the data to be converted. It doesn't matter if it contains words
3) Use the Edit...Paste Special...Add menu item

Brad

 

[SkipVought]

that you need numbers rather than text

I strongly disagree. These values look like IDENTIFIERS. In some cases, numeric IDENTIFIERS include LEADING ZEROS, like, for instance US ZIP codes. Identifiers ought to be TEXT not numbers. Often an identifier starts out as all numeric characters, but then, as the number of records increase, there is a need to EXPAND the capacity of the identifier, within a define number or characters, and ALPHA characters are then added. This can lead to problems querying is there are both NUMBERS and TEXT in the same column.

If a field is not used to perform arithmetic and is used as an identifier, it should be a TEXT field!

faq68-6659

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Hakala]

Thank you both! I was able to get the lookup to work by using your tips. This forum rocks.


Michelle Hakala

http://www.winebird.com/