Excel reference - connecting a list to a database

Solution 1:

As others have mentioned in the comments, this is a simple use case for VLOOKUP. Here's an example. I used a sheet titled "Lookups" to hold the list of numbers/parts, and "Sheet1" to hold the list that references the table. Lookups is a simple sheet that just holds the data, without any formulas:

Sheet is also simple, and contains a simple VLOOKUP call:

The formula (=VLOOKUP(A2, Lookups!$A$2:$B$6, 2, FALSE)) is straightforward. In the table defined in Lookups!$A$2:$B$6 (which excludes the column headers, although this isn't strictly necessary), VLOOKUP finds the row whose first element matches the value in A2, and returns the value in the column specified by the third argument, which in this case is the column containing the part name. The boolean argument FALSE tells Excel to perform an exact match, so if you enter data in column A that isn't found in the table, e.g. "10", as I've done, Excel will return #N/A instead of the last value in the table.

This exact formula does what you're looking for. The error you mentioned in the comment sounds like you may have some data validation problems or an older version of Excel, but without more details, I can't address that part of your question.