If the only allowed entries in A1 are the entries in that 1st column, I would
start by using Data Validation with that list of entries as the source. That
would guarantee that one of them is always chosen.
Then in B1 I would use a VLOOKUP() to match the entry in A1 with its
corresponding entry in the 2-column table and return the associated value
from the second column.
If entries in A1 can be something not from that list in the 2-column table,
then the way you have things now will probably still work, we just have to
modify things in the formula in B1 a little.
I'm going to assume that the entries in the first column of the 2-column
table are on a sheet other than the one where A1 and B1 are involved then to
use it as a list we need to give it a name. Select all of the entries in
that column and in the Name Box, enter a name such as EntryList and press the
[Enter] key. The Name Box is that place right above the 'A' marking column A
and the '1' marking row 1 that usually shows the address of the cell you have
currently selected.
Now we go to cell A1 and choose Data | Validation and then choose List from
the items under "Allow:" and then in the "Source:" area, enter
=EntryList
and close the dialog window.
in B1 enter a formula similar to this:
=VLookup(A1,'Sheet2'!A1:B18,2,False)
Sheet2 should be the name of the sheet that the 2-column table is on, and
A1:B18 should be the cells that define the top-left/bottom right area of that
2-column table.
Now you choose from your list in A1 and related item from the table shows up
in B1. But if what you enter in A1 is not in the list or the cell is still
blank, then #NA is going to appear in B1. Change the formula a little, and
that gets fixed:
=IF(ISNA(VLookup(A1,'Sheet2'!A1:B18,2,False)),"No
Match",VLookup(A1,'Sheet2'!A1:B18,2,False))
or you could change the ,"No Match", to ,"", to show just a blank in B1 when
no match is made, or ,0, to show a zero when no match is made.
Hope this helps.
