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:

Excel display default value based on another cell

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.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: Use VLOOKUP to Return Blank Instead of #N/A
Excel: Use VLOOKUP to Find Value That Falls Between Range
Excel: Use VLOOKUP to Return Last Matching Value

Leave a Reply

Your email address will not be published. Required fields are marked *