My company manufacturers a product line with variations of length. I have a workbook with a sheet for production that shows cut-lengths and quantities. It has another sheet with a complete list of part numbers and required cut-lengths for each. In some cases, there are multiple part numbers with the same length. I want to enter the part number in the production sheet and have it automatically populate the cut length from the corresponding row on the part number sheet.
How can I link a cell to include data from the cut-length column on the part number sheet based on the part number that is entered on the production sheet?
For example, if I enter "AA", "BA", or "CA" in the production sheet, I want it to automatically show "47 5/8" from the DIM B column.
=INDEX($E$3:$E$6,SUM(MMULT(TRANSPOSE(N(ISNUMBER(SEARCH(B9,$A$3:$C$6)))),ROW($1:$4))))You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
