You need a basic VLOOKUP formula. In your example image, in cell
G11 enter the formula
=VLOOKUP(F11,A:C,2,FALSE). In cell
H11 you would need
F11 contains your lookup value,
A:C is the cell range for your table and the value following that is the
col_index value (the column of the cell range) to be returned. (If
2 would return the value in column
The last bit which is the
range_lookup value controls whether value needs to match exactly or not.
FALSE to require an exact match and
TRUE to allow a non-exact match.
TRUE (the default setting which is also used if
range_lookup is not set) you need to make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, VLOOKUP may return an incorrect or unexpected value.
FALSE (require exact match), values in the first column of table do not need to be sorted.
You have already got your drop down boxes sorted but for completeness, to get drop down lists in column
- Select column
F. (You can position the drop-down list in most any cell or even multiple cells.)
Validation from the Data menu.
List from the
Allow option's drop-down list.
- Click the
Source control and simply enter the reference
- Make sure the
In-Cell Dropdown option is checked.