Home

Scanner Inventory with Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-326287%22%20slang%3D%22en-US%22%3EScanner%20Inventory%20with%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-326287%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%2C%20I%20am%20trying%20to%20correlate%20my%20inventory%20with%20excel%20and%20a%20scanner.%20I%20have%20my%20inventory%20items%20in%20an%20excel%20file%20and%20a%20USB%20scanner%20to%20scan%20the%20products.%20The%20scanner%20will%20output%20the%20physical%20inventory%20as%20follows%3B%20(12%20digit%20UPC%20code)%2C%20Quantity.%20For%20example%3B%20015839000015%2C%2012.%20The%20scanner%20will%20upload%20this%20into%20a%20single%20excel%20cell.%20I%20need%20to%20be%20able%20to%20look%20up%20the%20UPC%20code%20in%20my%20inventory%20sheet%20and%20input%20the%20quantity%20into%20the%20correct%20cell.%20What%20is%20the%20formula%20for%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-326287%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-326322%22%20slang%3D%22en-US%22%3ERe%3A%20Scanner%20Inventory%20with%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-326322%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20you%20can%20extract%20the%20UPC%20code%20by%20taking%20everything%20to%20the%20left%20of%20the%20comma%2C%20for%20example%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3Dleft(A1%2Cfind(%22%2C%22%2CA1)-1)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20can%20be%20plugged%20into%20a%20lookup%20formula%2C%20for%20example%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3Dvlookup(left(A1%2Cfind(%22%2C%22%2CA1)-1)%2C%24B%242%3A%24D%24200%2C3%2Cfalse)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20most%20important%20thing%20here%20is%20that%20the%20lookup%20value%20and%20the%20first%20column%20of%20the%20lookup%20table%20must%20be%20the%20same%20data%20type%2C%20i.e.%20the%20LEFT()%20function%20returns%20text%20and%20the%20Vlookup%20will%20only%20work%20if%20there%20are%20exact%20matches%20of%20that%20text%20in%20the%20lookup%20table.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
ajones0125
Occasional Visitor

Greetings, I am trying to correlate my inventory with excel and a scanner. I have my inventory items in an excel file and a USB scanner to scan the products. The scanner will output the physical inventory as follows; (12 digit UPC code), Quantity. For example; 015839000015, 12. The scanner will upload this into a single excel cell. I need to be able to look up the UPC code in my inventory sheet and input the quantity into the correct cell. What is the formula for that?

 

1 Reply

Hello, you can extract the UPC code by taking everything to the left of the comma, for example

 

=left(A1,find(",",A1)-1)

 

This can be plugged into a lookup formula, for example

 

=vlookup(left(A1,find(",",A1)-1),$B$2:$D$200,3,false)

 

The most important thing here is that the lookup value and the first column of the lookup table must be the same data type, i.e. the LEFT() function returns text and the Vlookup will only work if there are exact matches of that text in the lookup table. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies