Excel: Find & Display Name List from Two Separate Columns
In this article, we will focus on a common task in Microsoft Excel: finding and displaying a list of names from two separate columns. This can be helpful in many scenarios, such as combining a list of first names and last names, or merging data from multiple sources.
Finding the Names
The first step is to find the names in the two separate columns. You can use the VLOOKUP or INDEX and MATCH functions to look up values from one column based on values from another column.
Using VLOOKUP
The VLOOKUP function looks for a value in the first column of a table and returns a value in the same row from another column. In our case, we can use VLOOKUP to find a name in one column and return the corresponding name from the other column.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup\_value: the value to look up
- table\_array: the table containing the lookup value and the return value
- col\_index\_num: the column number in the table from which to return the value
- [range\_lookup]: (optional) whether to find an exact match or an approximate match
Here's an example of using VLOOKUP to find a name in a list of first names and return the corresponding last name:
=VLOOKUP(A2, A2:B9, 2, FALSE)
Using INDEX and MATCH
Another way to find a value in one column and return a corresponding value from another column is to use the INDEX and MATCH functions together. This approach is more flexible than VLOOKUP because it can look up values in any column and return a value from any other column.
=INDEX(return_array, MATCH(lookup_value, lookup_array, [match_type]))
- return\_array: the array containing the return value
- lookup\_array: the array containing the lookup value
- [match\_type]: (optional) the match type (0, 1 or -1)
Here's an example of using INDEX and MATCH to find a name in a list of first names and return the corresponding last name:
=INDEX(B2:B9, MATCH(A2, A2:A9, 0))
Displaying the Names
After finding the names from the two separate columns, you may want to display them in a single column or a table for easy viewing. You can use the CONCATENATE or & operator to combine the first and last names, and the TRANSPOSE function to display the names in a table.
Combining First and Last Names
To combine the first and last names, you can use the CONCATENATE function (Ctrl + Shift + &) or the & operator.
=CONCATENATE(first_name, " ", last_name)
or
=first_name & " " & last_name
Displaying the Names in a Table
To display the names in a table, you can use the TRANSPOSE function to convert a row of names into a column.
=TRANSPOSE(range_of_names)
Microsoft Excel provides various functions to find and display names from two separate columns. You can use VLOOKUP or INDEX and MATCH to find the names, and CONCATENATE, & or TRANSPOSE to display them in a single column or table. By mastering these functions, you can save time and increase productivity in your daily Excel tasks.