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

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Lookup Value in Column and Return Value of Another 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.
=LOOKUP(B8,B5:B9,D5:D9)

Use LOOKUP Formula to Lookup Value in Column and Return 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 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.
=VLOOKUP(B7,B5:D9,3, FALSE)


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.
=VLOOKUP("AA-03#",B5:D9,3,TRUE)


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.
=IFERROR(VLOOKUP(B8,B5:D9,3,FALSE),"Not Found")

Combine VLOOKUP and IFERROR Functions


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.
=INDEX(D5:D9,MATCH(B12,B5:B9,0))

Formula Breakdown

MATCH(B12,B5:B9,0) —> finds the matched value location in range B5:B9.
Output: 4

INDEX(D5:D9,MATCH(B12,B5:B9,0))
INDEX(D5:D9,4)—> returns the fourth value in the range D5:D9.
Output: 256


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.
=TEXTJOIN(", ",TRUE,IF(B12=B4:B9,D4:D9,""))

Combine TEXTJOIN and IF Functions


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Syeda Fahima Nazreen
Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo