Skip to main content

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?


Compare two columns and return value from third column with VLOOKUP function

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:

=VLOOKUP(D2,$A$2:$B$16,2,FALSE)   (if the value not found, an #N/A error is displayed)
=IFERROR(VLOOKUP(D2,$A$2:$B$16,2,FALSE), "")    (if the value not found, an empty cell is displayed)

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:


Compare two columns and return value from third column with INDEX and MATCH functions

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:

=INDEX($B$2:$B$16, MATCH(D2,$A$2:$A$16,0))    (if the value not found, an #N/A error is displayed)
=IFERROR(INDEX($B$2:$B$16, MATCH(D2,$A$2:$A$16,0)), "")    (if the value not found, an empty cell is displayed)

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:


If you are interested in VLOOKUP function in Excel, Kutools for Excel's Super LOOKUP supports some powerful Vlookup formulas for you, you can quickly do the Vlookup function without remembering any formulas. Click to download Kutools for Excel!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!


Vlookup multiple columns and return the corresponding values with INDEX and MATCH functions

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.

doc return value from third column 9

To dea with this job, please apply the following formula:

=INDEX($C$2:$C$16,MATCH(E2&F2, $A$2:$A$16&$B$2:$B$16,0))

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:


Compare two columns and return value form third column with a useful feature

Kutools for Excel’s Look for a value in list also can help you to return the corresponding data from another data range.

Note: To apply this Look for a value in list, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

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:

Download and free trial Kutools for Excel Now !


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 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

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns     Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolset12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   Many More...

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.

kte tab 201905


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!
Comments (36)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have 3 column table

COL_A, COL_B, COL_C
10 Yes 10
10 No. -

If column a value 10 and column b is Yes then Col c need col_a value, if Col_B no then column C need "-"

Please suggest formula
This comment was minimized by the moderator on the site
Name score points
1 25
2 13
3 7
4 12
5 1
6 19
7 63
8 18
9 54
10 12
11 22
12 5
13 9


How do i compare the values in the Score column and assign a point value based on highest to lowest and ties (13 to 1, and the Two 12s get the same value)

Thanks in advance.
This comment was minimized by the moderator on the site
I want to derive a value from a third column regardless of the order that is presented. So here you have BB-112 : Sarah : Completed. I want it to say Completed even if the values are reversed, ie Sarah : BB-112 : Completed. How can I make the order irrelevant?
This comment was minimized by the moderator on the site
Hello Jotari,Glad to help. Actually the easist way to achieve your goal is by using the new XLOOKUP function. For example, to know the country and abr of the country acchording to the Telephone Code, we can use the formula =XLOOKUP(F2,$C$2:$C$11,$A$2:$B$11), and the country and abr of the country regardless of the order of the values. Please see the screenshou I uploaded here. And please notice that the XLOOKUP is only available in Excel 2020, Excel for web and Microsoft 365.Sincerely,Mandy
This comment was minimized by the moderator on the site
I'm using GoogleSheets, it doesn't seem to have the XLOOKUP function.
This comment was minimized by the moderator on the site
HI Team, I would like to Compare column A and Column B if we found a value in column A , then print the result in Column C, else check in column B if found the value in Column B, then print in Column C , if we didn't found any value in columns A or B, then print the result in Column C as no value found using MS Excel
Compare Columns A and B  value found both the columns A and B then, Print the Column Value A in Column C
Compare Columns A and B Value found in A  then pint the Column Value A in   Column CCompare Columns A and B Value not found in A and Value found in Column B then print the column value in Column CCompare columns A and B value not found in both the columns A and B, then print the columns C with No value found 
This comment was minimized by the moderator on the site
=INDEX($C$2:$C$16,MATCH(E2&F2, $A$2:$A$16&$B$2:$B$16,0)) is not working
This comment was minimized by the moderator on the site
I have data range, wherein I have date, Machine no & Site(under site I have mentioned where is the machine currently and if any repair has been done). So, what I need now is the last date of repair of that machine no. Can you please help?
This comment was minimized by the moderator on the site
I think the first VLOOKUP example has an error in it. The first value should be D2, not D3. This could be why some people are having problems. Just thought I should point that out. Excellent work, though, thanks!
This comment was minimized by the moderator on the site
Hello, Jason,Thank you for your comment, yes, as you said, the cell reference should D2, not D3, I have updated the formula.Thanks again!
This comment was minimized by the moderator on the site
what 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
This comment was minimized by the moderator on the site
I 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
This comment was minimized by the moderator on the site
I 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?
This comment was minimized by the moderator on the site
Hi, .
Could you explain your problem more detailed, or you can insert a screenshot here?
Thank you!
This comment was minimized by the moderator on the site
i'm facing the same issue,can u provide any formula for this type of calculation,i want result in another column.
This comment was minimized by the moderator on the site
Hi, guys,
Could you give your problem more detailed, or, you can insert a screenshot here?
This comment was minimized by the moderator on the site
Can u help me plz, i'm facing the same issue?
This comment was minimized by the moderator on the site
I 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
This comment was minimized by the moderator on the site
Please 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

Exampled below
This comment was minimized by the moderator on the site
I have 3 excel columns which have values like this,
Col_A Col_B Col_C
----- ----- -----
400 600
500 800
400 300
300 200
700 900
800 700
500 100
I want the values to be copied in Column C from Column B, which are not Mache with Column A values.
I mean just copy the values from Column B, which are not available in Column A.
Like below
Col_C
-----
600
200
100
Is there any excel formula using which I can achieve this?
This comment was minimized by the moderator on the site
Hello, Hamid,

May be the following article can help you:
https://www.extendoffice.com/documents/excel/3041-excel-compare-two-columns-and-list-differences.html

Please try it, thank you!
This comment was minimized by the moderator on the site
Great. Thanks. This was a good solution
This comment was minimized by the moderator on the site
Hello Skyyang,


Please can you assist me with my question on help with a formula above?
Please 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
This comment was minimized by the moderator on the site
I have a master list of film titles in a certain order that must stay in that order. I duplicate that list onto another spreadsheet in order to be able to manipulate it and it gets out of order because I need to group it according to what has been completed or not. On that duplicate list, I add ID numbers in the column next to the titles. After I have completed everything I need to do, I must add those ID numbers to the master list while keeping the title order on that master list. How can I match those IDs to the list without having to manually add them in the correct order?
This comment was minimized by the moderator on the site
Hello, Hailey,

May be the following formula may help you:

=VLOOKUP(A2,new!$A$2:$B$13,2,FALSE)

in the above formula, new is your duplicate sheet name, please replace it to your own.

Please try it, hope it can help you!
This comment was minimized by the moderator on the site
Hello Sir, and thanks for the formulas provided. Although I used the formula as should it gives me N/A, which as I can understand is because is not matching the criteria between Columns D and A as per your example. Now so you can have better understanding, in my workbook A2 is the criteria cell that you want to return the value based on, G1:G15359 is the column including the criteria to be compared with, A1:N15359 the data range that you want to use.

The formula is: =IF(ISNA(MATCH(Sheet2!A2,Sheet3!$G$1:$G$15359,0)),"",VLOOKUP(Sheet2!A2,Sheet3!$A$1:$N$15359,7,FALSE))


As you have noticed I am using data from two different sheets, although I don't think that this is where the real issue is, since when clicking on the NA error it indicates the Sheet2 A2 cell and the error given is: The cell currently being evaluated contains a constant. (I have checked and confirmed that the formats are set on general). Not sure if it's because the information text are emails or because in some cells there's nothing.


Will be looking forward for your response.
This comment was minimized by the moderator on the site
Hello, Andresa,

I tried your formula and it works well in my excel workbook. Can you give an attachment or a screenshot, so that i can understand well.

Thank you!
This comment was minimized by the moderator on the site
I need to do a comparison and pull put data as below -

Sheet 1 contain Column A & B, B is blank. Sheet 2 contains Column C & D.


The entire Column C items in Sheet 2 to be compared with first row item in Column A and if any corresponding values/data are there in Column A, then Column B to be populated with data corresponding to the row item in Column D.

Column C will have a single word. Column D may or may not have data in it. Column A will have more text.
This comment was minimized by the moderator on the site
Hello,
Could you give a detailed example for your problem?
You can insert a screenshot or attachment.
Thank you!
This comment was minimized by the moderator on the site
I used this formula and it mostly worked but the data from the other sheet does not come on the same row to match the reference Criteria cell.


Here's my formula. can you have a look at it and see if there's anything wrong

=IF(ISNA(MATCH(DPU!C2,$A$2:$A$100,0)),"",VLOOKUP(DPU!C2,DPU!C2:AP100,2,FALSE))
This comment was minimized by the moderator on the site
Hello, Ardi,
If you want to vlookup from another worksheet, you should apply the following formula:
=IF(ISNA(MATCH(A2,Sheet1!$A$2:$A$10,0)),"",VLOOKUP(A2,Sheet1!$A$2:$B$10,2,FALSE))

Note: Sheet1 is the sheet contains the original data that you want to vlookup, and you should chaneg the cell references to your need.

Please try it! Thank you!
This comment was minimized by the moderator on the site
Great brother! My first formula is working. But 2nd formula not working. I tried many times. But i can't...
This comment was minimized by the moderator on the site
wow, so great! good sharing thank you! it was so useful for me.
This comment was minimized by the moderator on the site
You just saved me months of stressful entries with this post. I'm so grateful! Thanks.
This comment was minimized by the moderator on the site
Thank you some much it was very useful. Need add info, what if we have duplicate value in Col B, how to return the value for that too.
This comment was minimized by the moderator on the site
GOOD KNOWLEDGE SHARING THANK YOU VERY MUCH
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations