Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Match Two Columns and Output a Third in Excel (3 Quick Methods)

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.


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.

Data set for Match Two Columns and Output a Third in Excel


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.

Use of the VLOOKUP Function to Get Result from a Third Column

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)

Use of the VLOOKUP Function to Get Result from a Third Column

Step 4:

  • Now, press Enter.

Step 5:

  • Pull the Fill Handle icon to the last cell containing data.

Use of the VLOOKUP Function to Get Result from a Third Column

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.

Use of the VLOOKUP Function to Get Result from a Third Column

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)),"")

INDEX+MATCH+IFERROR to Get Output from a Third Column

Step 2:

  • Then, press Enter.

Step 3:

  • Pull the Fill Handle icon the Cell F9.

INDEX+MATCH+IFERROR to Get Output from a Third Column

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:


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))

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

Step 3:

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

Step 4:

  • Drag the Fill Handle icon.

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

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.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo