I've got a worksheet that uses the below vlookup to check on sheet 2 for a list of part numbers to put the correct internal works order which is in the column next to it.
This works quite well, we paste in the list of required parts and vlookup fills out the internal works orders saving us looking them up every week.
Problem comes when the part number isn't in the table, Vlookup seems to pick the one thats closest to it in the sorted column, so if we have Widgit1 to Widgit5 listed but not Widgit6 it will vlookup the works order number to Widgit5
Causes quite a bit of grief because the wrong thing gets booked out!
So how can I change it so that if there are no matches vlookup returns something like "not listed" or something rather than putting in the wrong thing?
I've tried adding a ZZZZZ with "not listed" as the works order but that doesn't work.
Code:
=VLOOKUP(A2,Sheet2!$A$2:$B$73,2)Problem comes when the part number isn't in the table, Vlookup seems to pick the one thats closest to it in the sorted column, so if we have Widgit1 to Widgit5 listed but not Widgit6 it will vlookup the works order number to Widgit5
Causes quite a bit of grief because the wrong thing gets booked out!
So how can I change it so that if there are no matches vlookup returns something like "not listed" or something rather than putting in the wrong thing?
I've tried adding a ZZZZZ with "not listed" as the works order but that doesn't work.
