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
Highlighted
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
Highlighted

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.