Filtered Lookup Columns
Out of the box, SharePoint 2010 has a lookup column which retrieves data from another list or library. But what if you want to limit the entries in the lookup drop down? The Filtered Lookup column type is the answer. This column type requires the installation of a farm solution which can be downloaded (free) from CodePlex. Once installed (added and deployed by your farm admin – see instructions), you will notice a new column type called Filtered Lookup.
When selected, you will notice that you can pick a site, a list or library and a column to use as drop down title. Additionally, you can specify a certain view (which could have its own filters) to limit choices, or you can build a custom CAML query.
In the example here, I created a custom view from the first list based on a choice column, then selected to filter by view and specified Large Cities as my view (filtered by a choice field with options like Small, Large, ..). In the second list, the city lookup field will only display cities where the choice field is equal to Large.
In CAML language (if Apply Query Filter is selected), you have to write XML-like syntax:
See more about CAML at MSDN or just use views if CAML scares you.
You can check the Add Multiple Values which will allow you to use more than one value from the lookup list (two lists interface), but the downside, if you consider it as such, in the inability to show other fields from the lookup list. You can still click the link to view details on a per item level.
Note: you can use InfoPath for lookup and filtering if you have it enabled.