How to compare two columns and return values from the third column in Excel?
For example, I have the following two columns, column A is some projects, and column B is the corresponding names. And here, I have some random projects in column D, now, I want to return the corresponding names from column B based on the projects in column D. How could you compare the two columns A and D and return the relative values from column B in Excel?
The VLOOKUP function can help you to compare two columns and extract the corresponding values from the third column, please do as follows:
1. Enter any of the below two formulas into a blank cell besides the compared column, E2 for this instance:
Note: In the above formulas: D2 is the criteria cell that you want to return the value based on, A2:A16 is the column including the criteria to be compared with, A2:B16 the data range that you want to use.
2. Then press Enter key to get the first corresponding value, and then select the formula cell and drag the fill handle down to the cells that you want to apply this formula, and all the corresponding values have been returned at once, see screenshot:
In Excel, the INDEX and MATCH functions also can help you to solve this task, please do as follows:
1. Enter any of the below two formulas into a blank cell where you want to return the result:
Note: In the above formulas: D2 is the value which you want to return its relative information, A2:A16 is the list that contains the value you want to return, B2:B16 is the column that you are looking for.
2. Then press Enter key to get the first corresponding value, and then select the formula cell and copy to the rest cells you need, and all the corresponding values have been returned, see screenshot:
Sometimes, you may have a range of data which contains three columns, now you want to lookup on the table to match two criteria values, if both the two values matches, it will return the data from the third column C.
To dea with this job, please apply the following formula:
Note: In the above formulas: E2, F2 are the criteria cells that you want to return the value based on, C2:C16 is the column that contains the values you want to return, A2:A16, B2:B16 are the columns you are looking for.
Then press Ctrl + Shift + Enter keys together to get the first result, see screenshot
And then copy and fill this array formual to other cells, and you will get the below result:
Kutools for Excel’s Look for a value in list also can help you to return the corresponding data from another data range.
After installing Kutools for Excel, please do as this:
1. Click a cell where you want to put the matched result.
2. Then click Kutools > Formula Helper > Formula Helper, see screenshot:
3. In the Formulas Helper dialog box, please do the following operations:
- In the Formula Type drop down list, please select Lookup option;
- Then, select Look for a value in list option in the Choose a formula list box;
- And then, in the Arguments input text boxes, select the data range, criteria cell and column you want to return matched value from separately.
4. Then click Ok, and the first matched data based on a specific value has been returned. You just need to drag the fill handle to apply this formula to other cells you need,see screenshot:
More relative VLOOKUP articles:
- Vlookup And Concatenate Multiple Corresponding Values
- As we all known, the Vlookup function in Excel can help us to lookup a value and return the corresponding data in another column, but in general, it can only get the first relative value if there are multiple matching data. In this article, I will talk about how to vlookup and concatenate multiple corresponding values in only one cell or a vertical list.
- Vlookup And Return The Last Matching Value
- If you have a list of items which are repeated many times, and now, you just want to know the last matching value with your specified data. For example, I have the following data range, there are duplicate product names in column A but different names in column C, and I want to return the last matching item Cheryl of the product Apple.
- Vlookup Values Across Multiple Worksheets
- In excel, we can easily apply the vlookup function to return the matching values in a single table of a worksheet. But, have you ever considered that how to vlookup value across multiple worksheet? Supposing I have the following three worksheets with range of data, and now, I want to get part of the corresponding values based on the criteria from these three worksheets.
- Vlookup And Return Whole / Entire Row Of A Matched Value
- Normally, you can vlookup and return a matching value from a range of data by using the Vlookup function, but, have you ever tried to find and return the whole row of data based on specific criteria.
- Vlookup And Return Multiple Values Vertically
- Normally, you can use the Vlookup function to get the first corresponding value, but, sometimes, you want to return all matching records based on a specific criterion. This article, I will talk about how to vlookup and return all matching values vertically, horizontally or into one single cell.
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!