VLOOKUP Returns a Temporary Zero

[Boyder9339]

Hi,

I have two lists of numbers and performing a vlookup of one of them to determine if the part was in the other. However, when I drag it down for each part number in the list, after several thousand lines it returns a "0" in the quantity rather than the value I want it to or #N/A. When I click on it and then click off it then shows the correct value. My equation is below.

=vlookup(C2,A:A,1,0)

 

[Joe4]

Welcome to the Board!

Sounds like you might have your calculation mode set to "Manual" instead of "Automatic".
Go to Options -> Formulas -> Calculation Options and choose the "Automatic" option.

 

[Marcelo Branco]

Welcome to Mr Excel forum

I don't understand what you are trying to do. Your formula wiil always return the lookup value (C2) if it's found in column A. Otherwise, returns #N/A.

Could you provide a small data sample (5 to 10 rows) along with expected results?

M.

 

[Boyder9339]

Advanced was already selected. I've attached a screenshot below.

A C D

564738357		XA4809459	XA4809459
494738356		R24809459	R24809459
504738356		R64809459	R64809459
474738356		TT4809459	TT4809459
R64738356		564809459	564809459
TT4738356		R24809454	R24809454

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

D is the column I am typing my equation, column A would not be in the same order as C, not would it contain exactly all the same numbers

 

[Marcelo Branco]

This?

	A​	B​	C​	D​
1​	Values​		Search​	Result​
2​	564738357​		XA4809459​	XA4809459​
3​	494738356​		R24809459​	R24809459​
4​	504738356​		R64809459​	Not Found​
5​	474738356​		TT4809459​	Not Found​
6​	R64738356​		564809459​	Not Found​
7​	TT4738356​		R24809454​	R24809454​
8​	R24809459​
9​	XA4809459​
10​	XXXXXXXX​
11​	R24809454​
12​	yyyyyyyy​

Formula in D2 copied down
=IF(C2="","",IF(ISNUMBER(MATCH(C2,A:A,0)),C2,"Not Found"))

Hope this helps

M.