Excel: Display Default Value Based on Another Cell

You can use the VLOOKUP function in Excel to display a default value in one cell based on another cell.

The following example shows how to do so in practice.

Example: Display Default Value Based on Another Cell in Excel

Suppose we have a list of basketball team names in column A along with ID numbers associated with each team in column B:

Now suppose we have another list of team names in column E and would like to use the values from column B as the default ID values for each team:

To display these default values in column F, we can type the following formula into cell F2:

`=VLOOKUP(E2, \$A\$2:\$B\$10, 2, FALSE)`

We can then click and drag this formula down to each remaining cell in column F:

Notice that the ID values in column F match the ID values in column B that are associated with each team.

How This Formula Works

Recall the formula that we used in cell F2:

`=VLOOKUP(E2, \$A\$2:\$B\$10, 2, FALSE)`

This formula uses the VLOOKUP function in Excel to look up the value in cell E2 within the range A2:B10 and then return the corresponding value from column 2 in the lookup range.

The last argument of FALSE specifies that we want to look for an exact match.

For example, the value in cell E2 is “Spurs” – the VLOOKUP function finds this value in the range A2:B10, then returns the value from column 2 of the range that corresponds to “Spurs” which turns out to be 1002.

The VLOOKUP function repeats this process for each team name in column E.