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.
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.
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
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.
- We add a column to make an Invoice.
- We will give input to the ID and Name box.
- Now, we will apply the VLOOKUP operation in Cell F6.
- Complete the formula and it will look like this:
- Now, press Enter.
- Pull the Fill Handle icon to the last cell containing data.
We see those names corresponding to each Product ID are showing.
- If we put any ID that is not present on our data set, see what happens.
- We put A-010 as product ID.
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.
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.
MATCH(lookup_value, lookup_array, [match_type])
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.
INDEX(array, row_num, [column_num])
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.
- Go to the Cell F6.
- Write the formula with proper arguments. So, the formula will be:
- Then, press Enter.
- Pull the Fill Handle icon the Cell F9.
Here, we compared two columns and get the output in the third column.
- 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.
This formula matches Cell E6 within the range B5 to B12. Here, 0 is used to get the exact match.
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.
This formula returns blank if the INDEX function result is invalid. Otherwise, it will be the result of the INDEX function.
- 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.
- Add three columns in the data to set the references.
- Now, give input on the reference boxes.
- Now, go to Cell D17.
- Write the formula here. The formula is:
- Then press Ctrl+Shift+Enter because it is an array function.
- 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)
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.