Lookup Value in Column and Return Value of Another Column in Excel

The corporate world works with huge datasets. Microsoft Excel has made their work easier through various formulas to maintain the datasets with ease. However, lookup for values in a column and get the return of another column of the relatable data is the most common thing in maintaining datasets. The article will explain five formula-based methods to lookup a value in one column and return the value of another column in Excel.

Download Practice Workbook

You can download the Workbook for practice from the link below.

5 Smart Ways to Lookup Value in Column and Return Value of Another Column in Excel

We will use the following dataset to explain five different formula-based methods to lookup a value in a column and return the value of another column in Excel. The article explains five different methods that include functions: LOOKUP, VLOOKUP, MATCH, INDEX, TEXTJOIN, etc. In order to explain the whole procedure, I have arranged a dataset. The dataset contains data related to the Sales_ID, the Date of Sales according to the ID number, and the Number of Sales that occurred for that particular ID number. Let us see the five methods to extract data from a column and get the result of a different column.

1. Use LOOKUP Formula to Lookup Value in Column and Return Value of Another Column

The topic itself features the formula name of this method. This is the shortest formula among all other methods. Let’s go through the following section for more details.

Steps:

• Apply the following formula in your preferred cell (i.e. C11) and press ENTER to lookup a value in one column and return the value of another column.

Read More: Excel VBA to Find Value in Column (6 Suitable Examples)

2. Adopt VLOOKUP Formula to Lookup Value in Column and Return Value of Another Column

There is another similar type of formula formed using the VLOOKUP function to lookup a value in one column and return the value of another column. Actually, there are two types of VLOOKUP formulas. One gives the exact match of the lookup value and the other gives a partial match.

2.1 VLOOKUP Formula for Exact Match

In order to have the exact match of the lookup value, we can use the VLOOKUP formula.

Steps:

• First of all, select a cell based on your preferred cell (i.e. C11).
• Now, insert the following formula in that cell to lookup a value in one column and return the value of another column.

2.2 VLOOKUP Formula for Partial Match

We can also use the VLOOKUP formula based on the partial match to lookup a value in one column and return the value of another column.

Steps:

• Input the following formula and press ENTER to lookup a value in one column and return the value of another column.

3. Combine VLOOKUP and IFERROR Functions to Lookup Value in Column and Return Value of Another Column

However, you can use another formula using VLOOKUP nested with IFERROR to look up the value in a column and return the value of another column as result.

Steps:

• Pick a cell (i.e. C11) and input the following formula in that cell to lookup a value in one column and return the value of another column.

4. Merge INDEX and MATCH Functions to Lookup Value in Column and Return Value of Another Column

Apart from that, you can use nested INDEX and MATCH formulas to lookup for a value in a column and get the result of another column in the dataset.

Steps:

• Apply the following formula in your preferred cell (i.e. C12) and press ENTER to lookup a value in one column and return the value of another column.

Read More: How to Get Cell Value by Row and Column in Excel VBA

5. Combine TEXTJOIN and IF Functions to Lookup Value in Column and Return Value of Another Column

Lastly, we can also combine the TEXTJOIN and IF functions to lookup for a value in a column and get the result of the value from another column.

Steps:

• Choose a cell based on your preference (i.e. C11).
• Now, insert the following formula in that cell to lookup a value in one column and return the value of another column.

Read More: How to Find Value in Column in Excel (4 Methods)

Conclusion

Eventually, the methods are based on formulas either nested or individual to lookup value in a column and return the value of another column in Excel. I hope this article has helped you solve your problem. For relatable articles, you can see the Related Readings section. If you have any further queries you can ask in the comment section.

Related Readings

• How to Find First Occurrence of a Value in a Column in Excel (5 Ways)
• Find Last Occurrence of a Value in a Column in Excel (5 Methods)
• How to Find Highest Value in Excel Column (4 Methods)
• Find Lowest Value in an Excel Column (6 Ways)
• How to Get Top 10 Values Based on Criteria in Excel
• Find Top 5 Values and Names in Excel (8 Useful Ways)