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?
Vlookup and return matching values from the third column:
Kutools for Excel collects multiple useful formulas for Excel Users, by using them you can quickly solve many difficult problems, such as calculate ages, add months, years, days, weeks, vlookup and return values from third column and so on.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
The following formulas 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:
2. Then press Enter key to get the first corresponding value, see screenshot:
3. And then select the 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:
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.
Kutools for Excel’s Look for a value in list also can help you to return the corresponding data from another data range.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.|
After installing Kutools for Excel, please do as follows:
1. Click a blank cell where you want to return the relative data.
2. Then click Kutools > Formulas > Look for a value in list, see screenshot:
3. In the Formula Helper dialog box, do the following operations:
(1.) Click button beside the Table_array to select the data range that you want to look from, and enter the $ character to the cell reference to make the relative reference to absolute reference;
(2.) Click button beside the Look_value to select the criteria value you need;
(3.) Click button beside the Column to select the column will be returned the corresponding values from.
4. Then click Ok button to close the dialog, and the first corresponding data has been displayed, see screenshot:
5. Then select the first matching cell and drag the fill handle down to the cells that you want to apply this formula, and all the relative values have been returned at once, see screenshot:
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 months agowhat if i have repeated valued in d column i.e with same name Q!,Q2,Q3,Q4 now if i use your formula i get only Q1 value i need also the 2nd,3rd,4th,matches also
To post as a guest, your comment is unpublished.· 2 months agoI want to compare 2 columns data on one sheet to a range on another sheet and return the data in the 3rd column from the 2nd sheet
To post as a guest, your comment is unpublished.· 3 months agoI have 3 columns, A B C, i'd like to get the value of A where the value in Column C matches the value in column B is that possible?
To post as a guest, your comment is unpublished.· 3 months agoHi, .
Could you explain your problem more detailed, or you can insert a screenshot here?
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 25 days agoCan u help me plz, i'm facing the same issue?
To post as a guest, your comment is unpublished.· 1 years agoI am struggling with this and just get blanks returning.
Want to say if cell Shhet1!ED1 matches a cell in column Sheet2!C:C then provide data for adjacent cell in Sheet2!A:A
To post as a guest, your comment is unpublished.· 1 years agoPlease could you assist me, I need to do an excel formula for a petty cash spreadsheet where it looks for an account number input in Col_C which must be looked for in a list of account numbers in Col_L and if FALSE needs to return the value input in COL_F