Excel, vlookup question

[peige]

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.

=VLOOKUP(A2,Sheet2!$A$2:$B$73,2)

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.

 

[james.miller]

you need to add a 4th argument (called range_lookup) and set it to false. ie

=VLOOKUP(A2,Sheet2!$A$2:$B$73,2,FALSE)

http://office.microsoft.com/en-gb/excel-help/vlookup-HP005209335.aspx

IF you dont then it will default to find the nearest match

 

[HeX]

You might also want to wrap the VLOOKUP in an IFERROR, so that if a part isn't in your list you don't just get a #N/A.

Something like:

=IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$73,2,FALSE),"No match")

 

[peige]

Ah ok, that looks like a fix. Thanks both!

You wouldn't believe the grief it caused, goods booked out in error, invoices raised.

 

[Skeeter]

Its a flaw that's been in the vlookup formula since day 0 that it defaults that final argument to True. Really annoying if you forget to add a False and end up with a random list of oddness.

 

[peige]

You might also want to wrap the VLOOKUP in an IFERROR, so that if a part isn't in your list you don't just get a #N/A.
Something like:
=IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$73,2,FALSE),"No match")

Added this today, job done!

Thanks all