VLOOKUP problem

[Tom]

Trying a vlookup formula one set of paroduct numbers has a little green
triganle in the upper left corner of the cell. The formula returns our
friend #N/A. I have tried formatting the entires (one is sent as text, the
other as general) as text (both) General (both) and as numbers. There ae no
trailing zeros or other odd spaces characters or what not. If I copy one
number to the other spreadsheet , it works but when you have several
hundereds of rows, that's not practical.

Any thought on what is problem/fix is here??

Thanx

 

[Niek Otten]

Formatting as numbers afterwards is not enough.
To make the text numbers:

- format as number
- copy an empty cell
- select your "numbers"
- Edit>Paste Special, check Add

Now they should be numbers

 

[Gary Brown]

Tools > Options > Error Checking > Settings
Uncheck the checkbox 'Enable Background Error Checking'
or
Tools > Options > Error Checking > Rule
Uncheck the checkbox 'Evaluates to Error Value'

 

[Tom]

Didn't change anything

Tools > Options > Error Checking > Settings
Uncheck the checkbox 'Enable Background Error Checking'
or
Tools > Options > Error Checking > Rule
Uncheck the checkbox 'Evaluates to Error Value'

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

 

[Tom]

Didn't work either

Formatting as numbers afterwards is not enough.
To make the text numbers:

- format as number
- copy an empty cell
- select your "numbers"
- Edit>Paste Special, check Add

Now they should be numbers

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

 

[Niek Otten]

It should. Check both the search argument and the table entries with ISTEXT
or ISNUMBER and check the length of both wit LEN().
What is your formula and especially the 4th argument? Is the table sorted
ascending?

 

[Dave Peterson]

And if use:
=isnumber(b4)

and then look at sheet4 column a and pick out a the cell that you "know"
matches, you could use:
=isnumber(sheet4!a99)
(change a99 to the correct cell address)

or
=b4=sheet4!a99

I'd try Niek's suggestion once more if you don't see a match.

 

[Niek Otten]

<they both check out as number>

Is that number format or is that the result of =ISNUMBER(Sheet4!$A$1) and
ISNUMBER(B4) check? BTW you'll have to check Sheet4!$A$1:Sheet4!$A$2317
Same for the length

<Sheet 4 is sorted 0-9> For column A?

 

[Tom]

This is after I format as "Number" prior to that one is number one is test.

What I'd like to do is find a common ground so this formula will work