Formulas

Question

If you have different numbers per line in the barcode column, you can see which product it actually is from these numbers. For example, 9040005530102: from the first two digits, you know that this product is DIM90. In the same barcode column, after scanning, you may also see other numbers such as 10040005590117, which is product DIM100. How can you automatically fill in the products column using a formula? For example, when 904XXXXXXX is scanned in the barcode column, display AAA in the products column.

I have 10 different products. SEE BELOW

904XXXXXXXXXX=DIM90

1004XXXXXXXXXX=DIM100

1104XXXXXXXXXX=DIM110

3004XXXXXXXXXX=DIM300

4004XXXXXXXXXX=DIM400

5050XXXXXXXXXX=DIM50

123220a-XXXXXXX=cim84A

123220b-XXXXXXX=cim84B

140039230035576000=CIM80A

140039230035669000=CIM80B

Anjanesh Vaidya · Accepted Answer

Hi @TVP ALAN,

I’ve created a nested IF formula to fulfill your requirement of automatically populating the Products column based on the scanned barcode values.

Please use the formula below:

=IF(LEFT(Barcode@row , 3) = "904", "DIM90", IF(LEFT(Barcode@row , 4) = "1004", "DIM100", IF(LEFT(Barcode@row , 4) = "1104", "DIM110", IF(LEFT(Barcode@row , 4) = "3004", "DIM300", IF(LEFT(Barcode@row , 4) = "4004", "DIM400", IF(LEFT(Barcode@row , 4) = "5050", "DIM50", IF(LEFT(Barcode@row , 7) = "123220a", "CIM84A", IF(LEFT(Barcode@row , 7) = "123220b", "CIM84B", IF(Barcode@row  = "140039230035576000", "CIM80A", IF(Barcode@row  = "140039230035669000", "CIM80B", ""))))))))))

This formula evaluates the barcode prefix (or full value where required) and fills in the correct product automatically when a barcode is scanned.

amarkumar · Accepted Answer

Hello @TVP ALAN use the below nested Ifs formula for you requirement:-

=IF([Barcode]@row  = "140039230035576000", "CIM80A", IF([Barcode]@row  = "140039230035669000", "CIM80B", IF(LEFT([Barcode]@row , 8) = "123220a-", "cim84A", IF(LEFT([Barcode]@row , 8) = "123220b-", "cim84B", IF(LEFT([Barcode]@row , 3) = "904", "DIM90", IF(LEFT([Barcode]@row , 4) = "1004", "DIM100", IF(LEFT([Barcode]@row , 4) = "1104", "DIM110", IF(LEFT([Barcode]@row , 4) = "3004", "DIM300", IF(LEFT([Barcode]@row , 4) = "4004", "DIM400", IF(LEFT([Barcode]@row , 4) = "5050", "DIM50", ""))))))))))