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

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

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

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

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

*is the column including the criteria to be compared with,*

**A2:A16***the data range that you want to use.*

**A2:B16****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,

*is the list that contains the value you want to return,*

**A2:A16***is the column that you are looking for.*

**B2:B16****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,

*is the column that contains the values you want to return,*

**C2:C16***,*

**A2:A16***are the columns you are looking for.*

**B2:B16**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 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.

### The Best Office Productivity Tools

#### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

**Reuse**: Quickly insert**complex formulas, charts**and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

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