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

=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

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.

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:

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

 🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions… Popular Features: Find, Highlight or 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  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ... 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 Toolsets:  12 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, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

#### 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!
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 "-"

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)