Quote Originally Posted by excelMTL View Post
Thank you for the quick response rcm! However, there is one problem with the MATCH function. If you look at the worksheet 'Parts Received' cell C16, the entry is 1234-05. In my master parts list on the first worksheet, there is in fact an entry with part number 1234. Since the bast part number is the same (the number before the dash), I require this to be marked as a match as well. Do you know of another way to match only the numbers before the dash?
OK I went back to the original worksheet. The dash problem can be solved by adding another column containing a dashless part number in the received parts sheet. Then the basis of comparison column in the match function should reference that column. I also noticed that the code in parts list is not always "matching" although visually it is. I applied the trim() function to both code entries in their respective sheets to ensure a true match. so, in the part received sheet I added the following for each entry:

for row 15:

in column H: =IFERROR(FIND("-",C15),0) this is stripping the part code from the dash and the rest thereof.

in column i: =TRIM(IF(H15>0,LEFT(C15,H15-1),C15)) if the code contains a dash just the left part of the code is taken, if not the whole code is recorded.

The match function in column G had to be =IF(IFERROR(MATCH(I15,'Parts List'!C:C,0),0)>0,1,0) since it needed to compare column I intead


Notice that the match function references parts list column c. That is because the part code written in column a has blanks embedded, so column c contains the trimmed version of column a.

This works and renders 174 parts.

I'll work a macro to avoid adding all this columns to your entries and send it asap.