VLookup Issue Returning NA or Wrong Value

M

MThies

New Member
Joined
Sep 1, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
When I use VLookup, I get #NA or the wrong value pulling. I am trying to get the "Product Item #" to populate next to the 1st UPC column. My formula is =VLOOKUP(A2,E2:F7790,2,FALSE).

Circle K Beer Add Delete List - Spring Reset_2.18.22.xlsx
ABCDEFG
1 UPCItem #Store #Product RetailUPCProduct Item #Item #
28066095755#N/A7038469000929211067421
38218403813#N/A70375301201481362
48769201369#N/A70876927523149815
53354400158#N/A70719903000514210
68982630040#N/A70341005750502010
78769201368#N/A70341005730602024
88200079896#N/A70719903006914209
984980600472#N/A70719900004814223
108378337550#N/A70341005750902009
1175452701021#N/A70341005763602027
1285146400373#N/A70341003034407134
138769201299#N/A70341000088005135
143354400149#N/A70341000163600123
158769201305#N/A70719904804516616
163354400146#N/A70341000875504854
173410057340#N/A70341005765302046
188066095765#N/A70341000734107033
198066095757#N/A70719904801916633
207289000119#N/A70341000871604810
218200078250#N/A100806609572167435
228769221231#N/A100806609564167034
238769201369#N/A100719903010414239
248982623055#N/A100341003034107133
258769201386#N/A100341000150900109
268200079876#N/A100806609571567423
273354400158#N/A100806609577667410
288982630040#N/A100806609571067424
298769201368#N/A100335449502767418
308200079896#N/A100806609561567013
3184980600472#N/A100806609571167430
328769201380#N/A100341000872204809
3384024560098#N/A100341001550501610
3475452701172#N/A100876921001247410
3575452701171#N/A100335449504467041
368769201299#N/A100806609561567013
372113618087#N/A100806609560567012
383354400149#N/A100341000150900109
393410004610#N/A100341001550901609
408769201305#N/A100341001563601627
412113618094#N/A100719903006914209
423410004609#N/A100341005750902009
4385146400385#N/A100405221008365015
448200077692#N/A100719903160014224
458066095695#N/A100341001750901209
468066095680#N/A100341000035702036
473354495027#N/A100719903200014238
488954053566#N/A100341000017323134
498769200991#N/A103341005725602018
508769201369#N/A103876927513449839
518769201368#N/A103820007238573610
5284980600472#N/A103806609575767413
538769201299#N/A103723116300985010
543354400149#N/A103341001750501210
558769201168#N/A103545270042189175
5685146400346#N/A103514640030885308
5785146400385#N/A103876920069749837
583410057340#N/A103806609579667431
598066095757#N/A103820007769573431
608066095765#N/A103820007621273572
618066095796#N/A103341000061522430
628200079902#N/A103341000035502035
637289000119#N/A103341005702302039
648769200490#N/A152719900952814246
6563598526038#N/A152341000000500135
668200075748#N/A152509340080897603
678769201369#N/A152820007321473568
6863598580099#N/A152723116301253012
698769201368#N/A152341005721502011
7063598580082#N/A152806609576567412
7175452700421#N/A152723116301253012
728378337550#N/A152719901702814012
738769200686#N/A152341005734002028
748769201299#N/A152341005725602018
752113618087#N/A152341000334100233
763410004610#N/A152719903006514235
778500003449#N/A152719904802416635
788500003448#N/A152719904801916633
7963598580038#N/A152341000032304853
808769201305#N/A152341005750902009
818066095765#N/A152876928313449807
827289000011#N/A152876920068649916
838200079902#N/A152221000017021523
848500003426#N/A152920000200022030
853354495042#N/A159359852458392631
863354495090#N/A159719903001714230
8789521600107#N/A159341000150500110
888066095643#N/A159341009891607402
898066095711#N/A159719904804516616
908769201369#N/A159341001550901609
918982623055#N/A159341001563601627
928200079876#N/A159719900004714023
933354400158#N/A159719903006914209
948982630040#N/A159341005763602027
9584980600472#N/A159719903160014224
968769201380#N/A159341005730602024
9784024560098#N/A159820007275473510
9875452700421#N/A159820007826373583
998378311010#N/A159804321034686012
1008769230050#N/A159804328028886001
10185146400357#N/A159885733310188010
10275452701171#N/A159523000043527333
1038769201299#N/A159523000054027323
1042113618087#N/A159221000028821509
1053410004610#N/A159335449504467041
1068769201305#N/A159335449504267040
1073354400146#N/A159806609560567012
1082113618094#N/A159335449504167039
10985146400346#N/A159876928210249701
1108200079902#N/A159876921001247410
1118066095683#N/A159837833752190003
1128769201369#N/A1213876921723149806
1138769201368#N/A1213158290111192906
1148769201299#N/A1213341000873004839
1158769201305#N/A1213876920080449930
1168769200117#N/A1213820007275173410
1178500002627#N/A1213728900001162412
1188500003449#N/A1213804328022886004
1198500003448#N/A1213804324032586002
1208500003426#N/A1213727200643437223
12175452701172#N/A1213523000254027123
1228800404091#N/A1213806609567567213
1238954053566#N/A1213876928313449807
12475452700421#N/A1216341005763602027
1258769200686#N/A1216727200643437223
1268769200378#N/A1216523000054027323
12763598510036S#N/A1223806609578567312
1288066095721#N/A1223723116301152912
1298769285231#N/A1223221000500221940
1308066095711#N/A1223719903012514205
1318769201369#N/A1223719901160014024
13263598580099#N/A1223719903001714230
1338982623055#N/A1223341000000900116
1348769201386#N/A1223341000082007135
1353354400158#N/A1223806609575567421
1368982630040#N/A1223837831100190121
1378769201368#N/A1223876920049049814
13863598580082#N/A1223876928323149809
13963598580100#N/A1223782500062724653
14063598580102#N/A1223341005167800126
1418769201380#N/A1223728900008362422
14284024560098#N/A1223876920095149940
1438218408050#N/A1223341005715201901
14475452700421#N/A1223719903000714211
14575452701171#N/A12234715247152
14672383001192#N/A1223405221106368172
14763598580108#N/A1223876920008449710
1488769201299#N/A1223806609564367035
1498500003449#N/A1223341000734407034
1508500003448#N/A1223341005734002028
15163598580038#N/A1223782500046424620
1528769201305#N/A1223876920067849917
1533354400146#N/A1223806609576567412
1542113618094#N/A1223728900047862420
1553410004609#N/A1223728900011962419
15663598510016S#N/A1223876920037849823
1578066095683#N/A1223806609531567513
1588066095643#N/A1223806609562567212
1598200079902#N/A1223876920066049739
1607031001539#N/A1223341000015200122
1616206703362#N/A1223876921001447413
1628769200378#N/A1223719900951817103
1638066095757#N/A1223359852241392856
1648954053566#N/A1223359851006292812
1658500003426#N/A1223820007862373429
1668769201369#N/A1224221000500221940
16763598580099#N/A1224341001563601627
16881582901153#N/A1224782500032824635
1698200079876#N/A1224335449509067419
1708200079896#N/A1224898261302852518
17184980600472#N/A1224335449506067409
17272383004612#N/A1224359855489092924
17363598580026#N/A1224341001530601624
1748769201305#N/A1224341000872204809
17563598524583#N/A1224898269003352323
1767199031600#N/A1224804328025486007
1778066095615#N/A1224820007912973620
1788066095643#N/A1224335449503367415
1798066095796#N/A1224359850101292850
1808769275231#N/A1224820007620073418
1818500003426#N/A1224876929710249813
1828769201369#N/A1231514640034685481
1833354400158#N/A1231359850674492848
1848982630040#N/A1231782500061424638
1858769201368#N/A1231719900004714023
18663598580100#N/A1231703100124486025
1878769201380#N/A1231804321008786003
18875452700421#N/A1231620670336286608
18975452701021#N/A1231221000028821509
19085146400357#N/A1231341005730602024
1918378337555#N/A1231719903160014224
19272383001419#N/A1231341000163600123
19375452701171#N/A1231838201040173810
1948378337557#N/A1231719900952714241
19563598580108#N/A1231806609568367036
19663598580026#N/A1231898269002052423
1978769201299#N/A1231723111331255290
1982113618087#N/A1231728900002062423
1993354400149#N/A1231885734333388009
2008500003449#N/A1231545270002489009
2018500003448#N/A1231723112301253013
20263598580038#N/A1231838202348223483
2038769201305#N/A1231806609578767313
2043354400146#N/A1231728900001662413
2057289000119#N/A1231876920042449913
2068200079902#N/A1231335440001867045
2078769285231#N/A1231708970133111636
2088500003426#N/A1231341006260205123
2098769282102#N/A1232876920099149945
2108066095711#N/A1232341005167800126
2118769201369#N/A1232341000017323134
21263598580099#N/A1232387663612098701
2138769201386#N/A1232920000130668512
21481582901153#N/A1232405221001668171
21584024560006#N/A1232782500060524636
2168218426489#N/A1232782500038224619
2178200079876#N/A1232341007743538112
2183354400158#N/A1232341006234105133
2198982630040#N/A1232838201239373832
2208769201368#N/A1232820007290873550
22163598580082#N/A1232876920001749734
22263598580100#N/A1232885738000288056
22384980600472#N/A1232728900015262418
2248769201380#N/A1232728900057762404
22575452700421#N/A1232335449505167037
22675452701172#N/A1232806609568867019
Sheet2
Cell Formulas
RangeFormula
B2:B226B2=VLOOKUP(A2,E2:F7790,2,FALSE)
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
You need to lock the lookup table address:

=VLOOKUP(A2,$E$2:$F$7790,2,FALSE)

Other than that, it looks like quite a few of your items aren't in the data - at least not in the snapshot.
 
Upvote 0
RoryA said:
You need to lock the lookup table address:

=VLOOKUP(A2,$E$2:$F$7790,2,FALSE)

Other than that, it looks like quite a few of your items aren't in the data - at least not in the snapshot.
Click to expand...
Thanks for the response, Rory. I tried locking it up but am still running into the same issues on my end. It's weird, when I use "Ctrl+F" I can find the matching upc for the majority in the "RetailUPC" Column. I couldn't get the snapshot to include all of the upc's listed.
 
Upvote 0
Your first entry works for me after a copy and paste into a worksheet. If it doesn't for you, then I suspect one of the columns is stored as true numbers and the other is stored as text.
 
Upvote 0
Solution
RoryA said:
Your first entry works for me after a copy and paste into a worksheet. If it doesn't for you, then I suspect one of the columns is stored as true numbers and the other is stored as text.
Click to expand...
That fixed the issue. Thank you for your help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Formula returning #VALUE! help
2
Replies
10
Views
169
Peter_SSs
Peter_SSs
O
  • Solved
Index/Match, Nested IF statements, or Array Formula?
Replies
9
Views
164
Ol Reliable
O
C
  • Solved
VBA - Copy and paste a range of values N times
Replies
6
Views
439
cydmm
C
S
  • Solved
Copy Textbox values from UserForm to the worksheet
Replies
6
Views
327
AlphaFrog
AlphaFrog
M
  • Question Question
inputting formulas across multiple sheets with varying number of rows
Replies
3
Views
124
mrider123
M

Forum statistics

Threads
1,224,817
Messages
6,181,148
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top