Yes, the full list of all possible corresponding codes and plants will be the most consistent, and if there's a mistake in any code on Sheet2 you will be alerted to that fact by an error in the plant cell - which could be useful; it could make you ask the question do we have a new dept code? or has someone made a mistake?


It's a balancing act; do I have a long list (more difficult to maintain when there are department changes) or a shorter list where we know a given plant will be associated with a range of numbers (this is often the case with cost centre names)?
If you are going to use a full list then the 3rd argument of the MATCH function has to be 0 (it looks only for an exact match) and the dept numbers can then be in any order. Also it's easier to write the formula if you use VLOOKUP with the last argument being FALSE (also an exact match).

The two formulae are in columns D and E of Sheet2 of the attached.


If you want a friendlier thing to show than an error code, then you can enclose the formula in the IFERROR function, see cells F42 and F43 of Sheet2.

Attached Files

MSOfficeForums39050List.xlsx (30.2 KB, 9 views)