# How to Match Two Columns in Excel and Output a Third Column

**To find a match between two columns in Excel and output a third column, follow the steps below:**

- Select cell
**F2**. - Enter the formula:
**=INDEX($C$2:$C$10, MATCH(E2, $A$2:$A$10, 0))** - Hit
**ENTER**to insert the formula. - Drag down the formula using the
**Fill Handle**.

The formula will look up the value in cell **E2** in column **A** (**A2:A10**) and return a corresponding value from column **C**(**C2:C10**).

## Match Two Columns and Output Third in Excel Using INDEX and MATCH Functions

Here, I have a list of product names in the range **A2:A10** along with their corresponding category and sales information. Now I will try to retrieve the sales amount of some of the product names by combining the **INDEX **and **MATCH **functions.

First, I will compare the two columns of product names (column **A2:A10** & **E2:E5**). Based on the comparison, I will return data from a third column (column **F2:F5**).

**Syntax**

=INDEX(table_array, MATCH(lookup_value, lookup_array, [match_type]))

**Formula**

**=INDEX($C$2:$C$10, MATCH(E2, $A$2:$A$10, 0))**

**Formula Explanation**

The** MATCH** function finds the position of the value in cell **E2** (**Product A**) within the range **$A$2:$A$10** with an** Exact Match** (**0**). So, the output of **MATCH(E2, $A$2:$A$10, 0) **is **1, **which refers to the first row inside the** INDEX **function range. Then, it uses the** INDEX** function to fetch the intersecting value of the column **$C$2:$C$10** and row **1** which is **$1,150**.

To find a match between two columns in Excel and output a third column using the **INDEX** & **MATCH** functions, follow the steps below:

- Select cell
**F2.** - Type the formula:
**=INDEX($C$2:$C$10, MATCH(E2, $A$2:$A$10, 0))**. - Press
**ENTER**to insert the formula. - Use the
**Fill Handle**to copy the formula down.

The formula compared columns **A2:A10** and **E2:E10.** Based on the comparison, it returned the sales amount of the matched products only.

## Match Two Columns and Output the Third in Excel Applying VLOOKUP Function

The **VLOOKUP** is a flexible function that you can use to match two columns and output a third in Excel.

**Syntax**

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

**Formula**

**=VLOOKUP(E2,$A$2:$C$10,3,FALSE)**

**Formula Explanation**

The** VLOOKUP** function first searches for the **exact match** (**FALSE**) of the value in cell **E2** (**Product A**) within the range **$A$2:$C$10**. Then it returns the matched data (**$ 1150**) from the 3rd column (referred to **col_index_number**=3) of the lookup array **$A$2:$C$10**.

To match two columns in Excel and output a thrid column, you can employ the **VLOOKUP** function. Here’s how:

- Select cell
**F2**. - Insert the formula:
**=VLOOKUP(E2,$A$2:$C$10,3,FALSE)** - Press
**ENTER**to insert the formula. - Drag the
**Fill Handle**to copy down the formula.

To get the price of certain products, we have compared the values of column **C** (**C2:C10**) with **A2:A10**. In cells **F2:F5**, we can see the output.

## Match Multiple Columns and Output Third in Excel with INDEX & MATCH Functions

You can apply Vlookup in multiple columns using** INDEX** and **MATCH** functions to match more than two columns and output a third in Excel.

Here we have the product name lists with their category, stock, and sales. Now I want to know the **Sales** of some products. Here I will match the range **D1:D10** with the other two columns, product name (**A2:A10**) and category (**B2:B10**). I will store the result in cells **H2:H4**.

**Syntax**

**=INDEX(return_range, MATCH(concatenated_lookup_value, concatenated_range, 0))**

**Formula**

**=INDEX($D$2:$D$10,MATCH(F2&G2,$A$2:$A$10&$B$2:$B$10,0))**

**Formula Explanation**

The** MATCH** function finds the position of the value in concatenated cells **F2** and **G2** (**Product A **& **Electronics**) within the concatenated range **$A$2:$A$10** and **$B$2:$B$10** with an** Exact Match** (**0**). Hence the output of **MATCH(F2&G2,$A$2:$A$10&$B$2:$B$10,0) **is **1**, which refers to the first row inside the **INDEX **function range. Then it uses the** INDEX** function to fetch the **Sales **value of the column **$D$2:$D$10** and row **1** that returns **$1,150**.

To match multiple columns in Excel and output a third column using **INDEX** & **MATCH** functions:

- Select cell
**H2**. - Write the formula:
**=INDEX($D$2:$D$10,MATCH(F2,IF($B$2:$B$10=G2,$A$2:$A$10),0))** - Press
**ENTER**to see the formula result. - Double-click on
**Fill Handle**to autofill the formula.

The formula compares the sales values from the range **D2:D10** with the specified ranges of columns **A** and **B**. Then, it retrieves the price of the matched products and categories.

## Match Multiple Columns and Output Third in Excel Combining 3 Functions (IF, INDEX & MATCH)

If you want to match more than two columns and output a third in Excel, you can use the combination of** IF**, **INDEX,** and **MATCH** functions to do so.

**Syntax**

**=INDEX(return_range, MATCH(lookup_value, IF(lookup_value, lookup_array)))**

**Formula**

**=INDEX($D$2:$D$10, MATCH(F2,IF($B$2:$B$10=G2,$A$2:$A$10),0))**

**Formula Explanation**

The **INDEX** function retrieves the corresponding value in column **D** within the range **$D$2:$D$10 **and the **MATCH** function specifies the **Exact Match** (**0**) with the value in cell** F2** and then, it puts the condition with the function** IF**. **IF** function applies a condition whether the value in cell** G2** matches within the values in the range **$B$2:$B$10**, and after that, it returns the corresponding result of **Sales** (**$1150**)related to the range **$A$2:$A$10**.

To match multiple columns in Excel and output a third column using **INDEX**, **MATCH**, & **IF** functions:

- Select cell
**H2**. - Enter the formula in the cell:
**=INDEX($D$2:$D$10,MATCH(F2,IF($B$2:$B$10=G2,$A$2:$A$10),0))** - Press
**ENTER**to insert the formula. - Drag down the formula from
**H2**to**H5**with the**Fill Handle**.

This formula compared data from two columns (**A** and **B**) with the sales values in **D2:D10**. Now it returns the matched prices of the specified product name and category.

## Conclusion

In this guide, I have discussed 2 methods to match two columns and output a third in Excel. Additionally, I have also added two more guidelines to match multiple columns and output third in Excel. While working on a large spreadsheet, there will be situations to match two or more columns to return the result from a third column. I think this will be enough to resolve the issues.

## Frequently Asked Questions

### How do I do a VLOOKUP in Excel?

To perform a **VLOOKUP** in Excel, enter the formula **=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])**. Replace parameters with your specific data. For example, to find the price of a product based on its ID in a table **(A2:B10)**, use **=VLOOKUP(A1, A2:B10, 2, FALSE)** and press **ENTER**.

### How do I match 3 columns in VLOOKUP?

To match three columns in **VLOOKUP** in Excel, you can use a combination of the **CONCATENATE** or **&** operator to create a composite key. Use the **=A1 & B1 & C1** formula. Then, perform **VLOOKUP** on the composite key with the formula **=VLOOKUP(D1, YourTableRange, DesiredColumnIndex, FALSE)**.

### How do I compare 4 columns in Excel?

To compare four columns in Excel, follow the steps below:

- Select the range.
- Go to the
**Home**tab >**Conditional Formatting**. - Choose a
**New Rule**, and use a formula like**=COUNTIF($B$1:$D$1, A1)**. - Set the format, and apply the rule.

Repeat the process for the other three columns, changing the formula accordingly.