Have you considered using VLOOKUP()? It sounds like you'd have to nest it inside an IF(), along the lines of--

=IF( ISNA( VLOOKUP(PW_UPC, Master_Table, PW_UPC_Column, FALSE)), VLOOKUP(Trunc(PW_UPC/10), Master_Table_Alt, PW_UPC_Alt_Column, FALSE), VLOOKUP(PW_UPC, Master_Table, PW_UPC_Column, FALSE))

The IF() statement condition is whether or not VLOOKUP() fails trying to find the PW_UPC value in the first column of the Master_Table. If ISNA() returns True, it can't find it, and tries to find the PW_UPC value less the last digit [I'm assuming it's an integer, so division by 10 gives us what we want; if it's text, use something like Left(PW_UPC, Len(PW_UPC) - 1)] in the first column of Master_Table_Alt (presumably a sub-range of Master_Table), returning the value in whichever column is indicated by PW_UPC_Alt_Column. If ISNA() returns False, it found the PW_UPC in Master_Table and returns the value in whichever column is indicated by PW_UPC_Column.