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:

1. Select cell F2.
2. Enter the formula: =INDEX($C$2:$C$10, MATCH(E2, $A$2:$A$10, 0))
3. Hit ENTER to insert the formula.
4. 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:

1. Select cell F2.
2. Type the formula: =INDEX($C$2:$C$10, MATCH(E2, $A$2:$A$10, 0)).
3. Press ENTER to insert the formula.
4. 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:

1. Select cell F2.
2. Insert the formula: =VLOOKUP(E2,$A$2:$C$10,3,FALSE)
3. Press ENTER to insert the formula.
4. 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:

1. Select cell H2.
2. Write the formula: =INDEX($D$2:$D$10,MATCH(F2,IF($B$2:$B$10=G2,$A$2:$A$10),0))
3. Press ENTER to see the formula result.
4. 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:

1. Select cell H2.
2. Enter the formula in the cell: =INDEX($D$2:$D$10,MATCH(F2,IF($B$2:$B$10=G2,$A$2:$A$10),0))
3. Press  ENTER to insert the formula.
4. 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:

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

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