How to Match Two Columns and Return a Third in Excel

To match two columns and return a third in Excel, you can use the INDEX and MATCH functions. The INDEX function returns a value from a specific position in a range, while the MATCH function searches for a specific value in a range and returns its relative position.

Here's how to use the INDEX and MATCH functions together:

1. Organize your data in three columns. The first two columns should contain the values you want to match, and the third column should contain the values you want to return.
2. In a new cell, enter the following formula:

=INDEX(Return_Range, MATCH(1, (First_Value=First_Column_Range) * (Second_Value=Second_Column_Range), 0))

Replace Return_Range with the range of cells containing the values you want to return, First_Value with the first value you want to match, First_Column_Range with the range of cells containing the first set of values to match, Second_Value with the second value you want to match, and Second_Column_Range with the range of cells containing the second set of values to match.

3. Since this is an array formula, press Ctrl + Shift + Enter instead of just Enter. Excel will automatically wrap the formula in curly braces {} to indicate that it's an array formula.

Example

Let's say you have the following data:

A      B      C
1  Fruit  Color  Price
2  Apple  Red    $1.00
3  Apple  Green  $0.90
4  Banana Yellow $0.50
5  Grape  Purple $2.00

You want to find the price of a green apple.

1. In cell E1, enter the first value to match, which is "Apple".
2. In cell E2, enter the second value to match, which is "Green".
3. In cell E3, enter the following formula:

=INDEX($C$2:$C$5, MATCH(1, (E1=$A$2:$A$5) * (E2=$B$2:$B$5), 0))

4. Press Ctrl + Shift + Enter to input this as an array formula.

The result in cell E3 will be $0.90, which is the price of a green apple.