Scan Bar Codes into Excel, Match Numbers

  1. Inventory Items descriptions in colum A and their matching Items Barcodes in Colum B.
  2. I want to scan On Hand inventory Item Barcodes into Colum C and match them to their corrosponding Barcode in Colum B. 
  3. If the On Hand Scaned inventory Item Barcode from colum C find a match in Colum B, then turn Colum C Green containing the scanned barcode number or display "Match" in Colum E, also print the matching barcode number in Colum D adajcent to it's message.
  4. If the On Hand Scaned inventory Item from colum C does not find a match in Colum B, then turn Colum C Yellow containing the scanned barcode number or display "Item Missing" in Colum E.                         

I made a slite change to my above requirements in order to print the scanned barcode number in the cell next to it's adjacent message. I currently don't have the Excel skill yet to pull this off but that's why I sign up, to become better! 



Should the formula be copied in each cell of colum C, from the first cell which is C2 and each cell that contains a inventory barcode number?  
JimmyP57 (rep: 2) Oct 25, '17 at 7:32 am
Add to Discussion


Selected Answer

Enter this formula in your cell E2 and copy down as far as there are values in column B.

=IF(ISNA(MATCH($B2,$C$2:$C$1000,0)),"Missing item","Matched")

The range C$2:C$1000 is arbitrary. 1000 is a number which should be higher than the number of items you expect to list in that sheet.

The formula works in the opposite direction to what you described. Like all Excel formulas it works from the inside towards the beginning. The MATCH function looks for the value of B2 in the defined range. If it doesn't find a match it will return an #N/A error. The ISNA function is designed to detect that error and will return True if an error occurs. This result is used by the IF function to return one of the two texts you want displayed.

When you first deploy the formula and column C is blank all items will be "Missing". Then, as you scan numbers into column C the texts will change.

You can do the colouring using conditional formatting. Look up the term in Excel help or google for it. You can also ask another question here. Since the CF will rely on the result of the MATCH function your question is sort of nested. On this forum we need to keep answers straight forward, each matched to a single question.



My apologies! The formula must be pasted in column E (not C). I have rectified the error in my answer above and added to it that it should be copied down as far as column B has values in it. Column C is used for your scanned bar codes, of course.
Variatus (rep: 813) Oct 25, '17 at 9:13 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login