Strange Vlookup #N/A error

[FaneDuru]

I use Vlookup function to bring some values from another Excel sheet. Lookup_value means numbers, but formated like text (Data -->Text to Columns-->Text) in both sheets.
What is strange is the fact that I receive 87 values for such codes but not for two of them. Both formated in the same way...
The formula used: =VLOOKUP(lookup_value, table_array, col_index_num, 0).
What else instead cell format should I have in mind?

Thanks in advance,
Fane Duru'

 

[SkipVought]

hi,

Cell Formats can be deceiving.

For instance, if you enter a number in a cell and then FORMAT that cell as text, ABSOLUTELY NOTHING CHANGES!!! It is STILL a NUMBER.

FORMAT changes NOTHING!

Whenever I have a column that contains text including all numeric characters, I enter values in that column PRECEEDED by an APOSTROPHY (the character that Excel uses to specify a 'label'). For instance...
[tt]
'123
'0123
[/tt]
There is a high probablility that your issue is related to this. In BOTH the cell containing the lookup value AND the corresponding cell in the lookup range, prefix an APOSTROPHY to your numeric character string.

Skip,

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

 

[FaneDuru]

Thanks Skip!

I found the mistake. Table_array address was not in absolute reference and Excel modified the range when I pulled the formula down. That two cases were in the first part of the table_array range and missed because of that.

But using Data -->Text to Columns -->Text not only the format is done. The number is really transformed in text. try it because it is useful.

 

[SkipVought]

But using Data -->Text to Columns -->Text not only the format is done. The number is really transformed in text. try it because it is useful.

Beleive me, I use that. However it is NOT a FORMAT. It is a CONVERSION of data from one thing to another. The result is TOTALLY DIFFERENT if TEXT or DATE is selected!

Skip,

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

 

[FaneDuru]

Correct. This is what I wanted to say in my last post but probably I was not so clear. In the first post I was wrong writing about formatting...

 

[SkipVought]

#N/A! error occurs when the lookup value does not exist in the lookup range.

So it's either the VALUE or the RANGE.

Skip,

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

 

[lionelhill]

Table_array address was not in absolute reference "

For exactly that reason, I prefer now to use named ranges for anything that will feature in a lookup.

 

[SkipVought]

Amen, and further, I hardly ever use VLOOKUP, because I either use Excel 2007+ Table references or Named Range Table Fields along with the INDEX & MATCH functions, as often my lookup field is not in the 'correct' relative position in the table.

Skip,

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

 

[xlbo]

tsk tsk Skip - I use vlookup to force me into better data design and have all my PKs to the left of my data (though I know index/match is faster and more flexible, I've always been a vlookup guy unless my hand is forced!)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question