Lookup Value in Column and Return Value of Another Column in Excel (2024)

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.

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

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 lookupa value in one column and return the value of another column.

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 functionto 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 theVLOOKUPformula.

Steps:

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

2.2 VLOOKUP Formula for Partial Match

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

Steps:

• Input the following formulaand press ENTER to lookupa 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 cellto lookupa 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 lookupa value in one column and return the value of another column.

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 cellto lookupa value in one column and return the value of another column.

Download Practice Workbook

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

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. If you have any further queries you can ask in the comment section.

Related Readings

• How to Check If a Value is in List in Excel
• How to Find Top 5 Values and Names in Excel
• Find Text in Excel Range and Return Cell Reference
• How to Search Text in Multiple Excel Files
• [Solved!] CTRL+F Not Working in Excel
• How to Get Top 10 Values Based on Criteria in Excel
• How to Create Top 10 List with Duplicates in Excel

<< Go Back to Find Value in Range | Excel Range | Learn Excel