Microsoft Excel is one the most widely used application of today’s world. From big corporate houses to small and medium enterprises use this application. We can process our data according to our desire through this application. In this article, we will explain how to match two columns and get output from the third column in Excel. This is used when we need a certain amount of data from a big datasheet.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**3 Methods to Match Two Columns and Output Third in Excel**

We will explain how to match two columns and output from the third in Excel using three simple methods. We take a data set of a super shop consisting of **Product ID** and **Name**.

**1. Use of the VLOOKUP Function to Get Result from a Third Column in Excel**

**The VLOOKUP function** looks for a value in the leftmost column of a table. And a value in the same row from a column we specify. By default, the table must be sorted in ascending order.

**Syntax:**

**VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])**

**Arguments:**

** lookup_value – **We look up this value through the operation. Our look value must be in the first column the specified data range mentioned by table_array. Lookup_value can be a value or a reference to a cell.

** table_array –** It is the range specified to search the lookup_value. It may be a named range or table or cell reference. The return value should be included here.

*col_index_num***–** This number indicates which column we will get in the return. It starts from the last column of table_array.

*range_lookup***–** It is a logical value. It specifies the nature of the lookup of the function. We have two options **Exact match** or an **Approximate match**.

In this section, we will use **the VLOOKUP function** to match columns.

**Step 1:**

- We add a column to make an
**Invoice**.

**Step 2:**

- We will give input to the
**ID**and**Name**box.

**Step 3:**

- Now, we will apply the
**VLOOKUP**operation in**Cell F6**. - Complete the formula and it will look like this:

`=VLOOKUP(E6,$B$5:$C$12,2,FALSE)`

**Step 4:**

- Now, press
**Enter**.

**Step 5:**

- Pull the
**Fill Handle**icon to the last cell containing data.

We see those names corresponding to each **Product ID** are showing.

**Step 6:**

- If we put any
**ID**that is not present on our data set, see what happens. - We put
**A-010**as product ID.

**Read More:** **VLOOKUP Formula to Compare Two Columns in Different Sheets!**

**2. INDEX+MATCH+IFERROR to Get Output from a Third Column in Excel**

**The IFERROR function** checks a value and if this is an error or not. If finds an error that case shows something given on the argument. Otherwise, it returns the value of the reference.

**Syntax:**

**IFERROR(value, value_if_error)**

**Arguments:**

** value –** It is the argument to check the error.

** value_if_error –** It is the value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

**The MATCH function** looks for a selected object in a given range. It also gives the relative position of that object in that range. We use **MATCH **instead of one of the **LOOKUP **functions if we need the position of the object in that range.

**Syntax:**

**MATCH(lookup_value, lookup_array, [match_type])**

**Arguments**:

** lookup_value –** It is the desired value we want to match in look_array. This lookup_value argument may be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

** lookup_array –** The given range of cells for search.

** match_type –** It may be -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

**The INDEX function** returns a value or cell reference to a value from a table or range. There are two ways to use the INDEX function: If we want to return the value of a stated cell or array of cells will use the Array form. Otherwise, we will use the Reference form to return a reference of stated cells.

**Syntax:**

**INDEX(array, row_num, [column_num])**

**Arguments:**

** array –** A range or an array constant. If the array contains only one row or column, the corresponding row_num or column_num argument is optional. If the array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in the array.

** row_num –** It is required unless column_num is present. It chooses the row in the array to return a value from that. If row_num is omitted, column_num is required.

* column_num –* It chooses a column in the array to return a value. If column_num is omitted, row_num is required.

Here, we will use the combination **IFERROR**, **MATCH**, and **INDEX **functions to match two columns and get output from a third one.

**Step 1:**

- Go to the
**Cell F6**. - Write the formula with proper arguments. So, the formula will be:

`=IFERROR(INDEX($C$5:$C$12,MATCH(E6,$B$5:$B$12,0)),"")`

**Step 2:**

- Then, press
**Enter**.

**Step 3:**

- Pull the
**Fill Handle**icon the**Cell F9**.

Here, we compared two columns and get the output in the third column.

**Step 4:**

- Now, will input a product ID that is not present on the data set.
- We put
**A-010**and see what happens.

We see that blank in case of any object that is not present on the data set.

**Formula Breakdown:**

**MATCH(E6,$B$5:$B$12,0)**

This formula matches **Cell E6** within the range **B5 **to **B12**. Here, **0** is used to get the exact match.

**Output: 2**

**INDEX($C$5:$C$12,MATCH(E6,$B$5:$B$12,0))**

This formula returns the value from the range **C5** to **C12**. The second argument of the **INDEX **function is the result of the **MATCH **function.

**Output: Oil**

**IFERROR(INDEX($C$5:$C$12,MATCH(E6,$B$5:$B$12,0)),””)**

This formula returns blank if the INDEX function result is invalid. Otherwise, it will be the result of the **INDEX **function.

**Output: Oil**

**Read More: Match Two Columns in Excel and Return a Third (3 Ways)**

**Similar Readings:**

**How to Compare Two Columns in Excel for Missing Values (4 ways)****How to Compare 4 Columns in Excel (6 Methods)****Excel Macro to Compare Two Columns (4 Easy Ways)****Macro to Compare Two Columns in Excel and Highlight Differences****Excel Compare Text in Two Columns (7 Fruitful Ways)**

**3. INDEX-MATCH Array Formula to Match Two Columns and Output from Third**

Here, we will use an array formula and compare two columns and get output from the third.

First, add a column with our data, so that we can get a return from that column.

**Step 1:**

- Add three columns in the data to set the references.
- Now, give input on the reference boxes.

**Step 2:**

- Now, go to
**Cell D17**. - Write the formula here. The formula is:

`=INDEX(D5:D12,MATCH(B17&C17,B5:B12&C5:C12,0))`

**Step 3:**

- Then press
**Ctrl+Shift+Enter**because it is an array function.

**Step 4:**

- Drag the
**Fill Handle**icon.

We tried to match two columns of the data set with another table and get results from the third column.

**Related Content:** **Excel formula to compare two columns and return a value (5 examples)**

**Conclusion**

In this article, we simply showed 3 methods to match two columns and get output from the third one in Excel. I hope this will satisfy your needs. Please have a look at our website **Exceldemy.com** and give your suggestions in the comment box.