VLOOKUP issue - some cells return values, some are N/A

[virtualcircus]

Hello everybody,

I am trying to do VLOOKUP based on value in Sheet 1 (List1) in Column C and some cells return right value while some are N/A.
I format them to number (both sheets) but it didnt solve the issue.
I noticed cells showing green triangle indication there is an error (claiming it is text cell) work while the number cell dont. When I tried to fix text cell by converting it to number, it return me N/A.

Would anybody be so kind and give me an assistance. I believe it is something trivial but i am already pissed off :-)
I need to VLOOKUP all Columns in Sheet 2 into Sheet 1.

Thank you in advance for any hints.

Bob

VLOOKUP_screen.PNGVLOOKUP_issue.xlsx

[romperstomper]

Try adding &"" to the lookup value in the formula - eg =VLOOKUP(A2&"",...

[sandy666]

or try Power Query

Please Login or Register  to view this content.

[curiouscat408]

In worksheet List2, all the data in column A is type text, even though the cell format is General.

But in worksheet List1, the data in column A is a mix of type text and type numeric, even though the cell format is Number.

Note the distinction between the type of cell data and the cell format. In comparing cell values, it is the type of the data that matters.

The format of the cell does not matter, and looks can be deceiving. Use formulas of the form =ISTEXT(A4) and =ISNUMBER(A2) to determine the type of cell value.

-----

Use the feature Data > Text To Columns to convert the entire column of data to one type or another in the third dialog box.

But the question: which type should they be?

Ostensibly, we might think that column A in List1 should be type Text, since that is the consistent type of column A in List2.

But that still causes some erroneous #N/A errors because, for example, 8866902 in A24 in List1 is 08866902 in A388 in List2.

So, I think the more reliable change is use Text To Columns to convert both column A in List1 and in List2 to General.

That changes the cell data type to numeric.

FYI, that still results in two #N/A errors in List1. The reason is: 70553912 in A50 and 5593581 in A204 in List1 are indeed missing from column A of List2.

-----

Note: We cannot simply set the format of column A in List2 to General or Number.

That does not change the type of the cell value, unless and until we re-enter the data.

-----

PS.... Since the data in column A are IDs, not numbers to be used in arithmetic, usually I would suggest that column A in List1 should indeed be type text.

And to handle the difference between List1!A50 and List!A204, I might suggest changing the VLOOKUP as follows:

=VLOOKUP("*" & A2, List2!$A$1:$G$1881, 2, 0)

However, that might cause other problems, since I do not know just how unique the substrings are.

[sandy666]

try

Please Login or Register  to view this content.

value 5593581 or 05593581 doesn't exist in list2 the same with 70553912

[virtualcircus]

Thank you very much to all responses. It works perfectly!

Cheers,

Bbo

[sandy666]

my pleasure

if your problem is solved you can hit Add Reputation to the posts which have helped you