VLookup Struggles

[jarett]

I do not use the VLOOKUP a lot and always struggle when I do. I know the most common reason for errors is the lookup value data not being in the most left column. I have tried putting the lookup columns on the same sheet and same result. What am I missing?

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Item Code	ProductLine	Description	PrimaryVendorNo	WarehouseCode	UDF_INVENTORIED_ITEM	TotalQuantityOnHand	LastPhysicalCountDate	UDF_UNIQUEKEY	UDF_UPC	StandardUnitCost	LastSoldDate
2	054X-BLACK	A	Hanes EcoSmart - 5.2-Ounce Jer	0001904	000	N	0.00				0.00		#N/A
3	054X-BLACK-2XL	A	Hanes EcoSmart - 5.2-Ounce Jer	0001904	000	N	0.00		63011	766369066612	8.53		#N/A
4	054X-BLACK-3XL	A	Hanes EcoSmart - 5.2-Ounce Jer	0001904	000	N	0.00		83702	766369066629	9.31	5/25/2023	#N/A
5	054X-BLACK-L	A	Hanes EcoSmart - 5.2-Ounce Jer	0001904	000	N	0.00		63004	766369066599	6.65	8/8/2023	#N/A
6	054X-BLACK-M	A	Hanes EcoSmart - 5.2-Ounce Jer	0001904	000	N	0.00		63003	766369066582	6.65	12/8/2023	#N/A
7	054X-BLACK-S	A	Hanes EcoSmart - 5.2-Ounce Jer	0001904	000	N	0.00		63002	766369066575	6.65		#N/A
8	054X-BLACK-XL	A	Hanes EcoSmart - 5.2-Ounce Jer	0001904	000	N	0.00		63005	766369066605	6.65		#N/A
9	054X-DEEP FOREST	A	Hanes EcoSmart - 5.2-Ounce Jer	0001904	000	N	0.00				0.00		#N/A
10	054X-DEEP FOREST-2XL	A	Hanes EcoSmart - 5.2-Ounce Jer	0001904	000	N	0.00		310961	766369121717	8.53		#N/A
11	054X-DEEP FOREST-3XL	A	Hanes EcoSmart - 5.2-Ounce Jer	0001904	000	N	0.00		310972	766369121724	9.31	5/25/2023	#N/A
12	054X-DEEP FOREST-L	A	Hanes EcoSmart - 5.2-Ounce Jer	0001904	000	N	0.00		310954	766369121694	6.65		#N/A
13	054X-DEEP FOREST-M	A	Hanes EcoSmart - 5.2-Ounce Jer	0001904	000	N	0.00		310953	766369121687	6.65		#N/A
14	054X-DEEP FOREST-S	A	Hanes EcoSmart - 5.2-Ounce Jer	0001904	000	N	0.00		310952	766369121670	6.65		#N/A
15	054X-DEEP FOREST-XL	A	Hanes EcoSmart - 5.2-Ounce Jer	0001904	000	N	0.00		310955	766369121700	6.65		#N/A
Sheet1

Cell Formulas
Range	Formula
M2:M15	M2	=VLOOKUP(I2,Sheet2!A1:B25,2,FALSE)

Named Ranges
Name	Refers To	Cells
Sheet2!_FilterDatabase	=Sheet2!$A$1:$B$1	M2

Book1.xlsx
	A	B
1	unique_key	my_price
2	62872	5.06
3	62873	5.06
4	62874	5.06
5	62875	5.06
6	62881	5.66
7	62912	6.84
8	62913	6.84
9	62914	6.84
10	62915	6.84
11	62921	8.78
12	62932	9.59
13	63002	6.84
14	63003	6.84
15	63004	6.84
16	63005	6.84
17	63011	8.78
18	64702	2.37
19	64703	2.37
20	64704	2.37
21	64705	2.37
22	65611	2.35
23	65612	2.35
24	65613	2.35
25	65614	2.35
26	65615	2.35
27	65621	2.65
28	65622	2.65
29	65623	2.65
30	65624	2.65
31	65625	2.65
32	65631	2.65
33	65632	2.65
34	65633	2.65
35	65634	2.65
36	65635	2.65
37	65641	2.65
Sheet2

 

[etaf]

works OK
if i change a value in sheet 2 to match column 1
like

i put 1 on sheet2 A2
and 1 on sheet1 I2

and returns the value in B

but the numbers in I have to match with a value in A

Book2
	A	B
1	unique_key	my_price
2	1	5.06
3	62873	5.06
4	62874	5.06
5	62875	5.06
6	62881	5.66
7	62912	6.84
8	62913	6.84
9	62914	6.84
Sheet2

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Item Code	ProductLine	Description	PrimaryVendorNo	WarehouseCode	UDF_INVENTORIED_ITEM	TotalQuantityOnHand	LastPhysicalCountDate	UDF_UNIQUEKEY	UDF_UPC	StandardUnitCost	LastSoldDate
2	054X-BLACK	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		1		0		5.06
3	054X-BLACK-2XL	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		63011	7.66369E+11	8.53		#N/A
Sheet1

Cell Formulas
Range	Formula
M2:M3	M2	=VLOOKUP(I2,Sheet2!A1:B25,2,FALSE)

 

[jarett]

works OK
if i change a value in sheet 2 to match column 1
like

i put 1 on sheet2 A2
and 1 on sheet1 I2

and returns the value in B

but the numbers in I have to match with a value in A

Book2
	A	B
1	unique_key	my_price
2	1	5.06
3	62873	5.06
4	62874	5.06
5	62875	5.06
6	62881	5.66
7	62912	6.84
8	62913	6.84
9	62914	6.84
Sheet2

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Item Code	ProductLine	Description	PrimaryVendorNo	WarehouseCode	UDF_INVENTORIED_ITEM	TotalQuantityOnHand	LastPhysicalCountDate	UDF_UNIQUEKEY	UDF_UPC	StandardUnitCost	LastSoldDate
2	054X-BLACK	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		1		0		5.06
3	054X-BLACK-2XL	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		63011	7.66369E+11	8.53		#N/A
Sheet1

Cell Formulas
Range	Formula
M2:M3	M2	=VLOOKUP(I2,Sheet2!A1:B25,2,FALSE)

Does the format of the cells have to match? I tried the same thing on my copies and it didn't work?

 

[etaf]

yes, they do need to be numbers and not text - usually that would mean the numbers are left justified

seems to work - i copied the formula again down the sheet

added to a drop box share - will be available for a few days

Vlookup sample - ETAF.xlsx

Shared with Dropbox

www.dropbox.com

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Item Code	ProductLine	Description	PrimaryVendorNo	WarehouseCode	UDF_INVENTORIED_ITEM	TotalQuantityOnHand	LastPhysicalCountDate	UDF_UNIQUEKEY	UDF_UPC	StandardUnitCost	LastSoldDate
2	054X-BLACK	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		1		0		5.06
3	054X-BLACK-2XL	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		63011	7.66369E+11	8.53		8.78
4	054X-BLACK-3XL	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		83702	7.66369E+11	9.31	5/25/2023	#N/A
5	054X-BLACK-L	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		63004	7.66369E+11	6.65	8/8/23	6.84
6	054X-BLACK-M	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		63003	7.66369E+11	6.65	8/12/23	6.84
7	054X-BLACK-S	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		63002	7.66369E+11	6.65		6.84
8	054X-BLACK-XL	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		63005	7.66369E+11	6.65		6.84
9	054X-DEEP FOREST	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0				0		#N/A
10	054X-DEEP FOREST-2XL	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		310961	7.66369E+11	8.53		#N/A
11	054X-DEEP FOREST-3XL	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		310972	7.66369E+11	9.31	5/25/2023	#N/A
12	054X-DEEP FOREST-L	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		310954	7.66369E+11	6.65		#N/A
Sheet1

Cell Formulas
Range	Formula
M2:M12	M2	=VLOOKUP(I2,Sheet2!A1:B25,2,FALSE)

 

[Asbestos_Jen]

Your list (Sheet2!A1:B37) is longer than your formula accommodates (Sheet2!A1:B25), so that could be the source of some errors . I had no problems, just #N/A errors where sheet1 column i was blank or did not match anything in the list (red).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Item Code	ProductLine	Description	PrimaryVendorNo	WarehouseCode	UDF_INVENTORIED_ITEM	TotalQuantityOnHand	LastPhysicalCountDate	UDF_UNIQUEKEY	UDF_UPC	StandardUnitCost	LastSoldDate
2	054X-BLACK	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0				0		#N/A
3	054X-BLACK-2XL	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		63011	7.66E+11	8.53		8.78
4	054X-BLACK-3XL	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		83702	7.66E+11	9.31	5/25/2023	#N/A
5	054X-BLACK-L	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		63004	7.66E+11	6.65	08-08-23	6.84
6	054X-BLACK-M	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		63003	7.66E+11	6.65	12-08-23	6.84
7	054X-BLACK-S	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		63002	7.66E+11	6.65		6.84
8	054X-BLACK-XL	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		63005	7.66E+11	6.65		6.84
9	054X-DEEP FOREST	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0				0		#N/A
10	054X-DEEP FOREST-2XL	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		310961	7.66E+11	8.53		#N/A
11	054X-DEEP FOREST-3XL	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		310972	7.66E+11	9.31	5/25/2023	#N/A
12	054X-DEEP FOREST-L	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		310954	7.66E+11	6.65		#N/A
13	054X-DEEP FOREST-M	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		310953	7.66E+11	6.65		#N/A
14	054X-DEEP FOREST-S	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		310952	7.66E+11	6.65		#N/A
15	054X-DEEP FOREST-XL	A	Hanes EcoSmart - 5.2-Ounce Jer	1904	0	N	0		310955	7.66E+11	6.65		#N/A
Sheet1

Cell Formulas
Range	Formula
M2:M15	M2	=VLOOKUP(I2,Sheet2!A1:B37,2,FALSE)

 

[Peter_SSs]

On the Formulas ribbon tab, check in the Calculation Options drop-down that your calculation is set to Automatic.

Also, your formula in M2 and copied down should have some $ signs
=VLOOKUP(I2,Sheet2!A$1:B$25,2,FALSE)

 

[jarett]

Made sure both columns were numbers format @etaf and used the formula @Peter_SSs recommended and got a good result, thanks!

 

[Peter_SSs]

Glad we were able to help. Thanks for the follow-up.