We'll reference the state list from the address list with the INDEX-MATCH. The state list is a list of states and their state codes. In this case, we have the "state list" and the "address list". Lookup_value: The thing you want to find the search array (ex. Lookup_array: A list of values you want to search against (ex. We'll replace these with MATCH statements. Row_num / col_num: Index typically operates on cell coordinates (ex.
![how to use vlookup in excel to match two columns how to use vlookup in excel to match two columns](https://www.excel-exercise.com/wp-content/uploads/2017/10/Compare_2_Columns_IF.png)
The INDEX-MATCH-MATCH formula: The formula we're using has three parts.įORMULA = INDEX(array, row_num, )Īrray: A list of values that live to the left or right of the search value (ex. This will be useful when we write the second MATCH formula. This will lock the row, but not the column. This will be useful when we write the first MATCH formula.Ībsolute row, relative column (A$1). This will lock the column, but not the row. We'll lock both the rows and columns when referencing the search array.Ībsolute column, relative row ($A1:$A20). This will lock your search on these cells. Here's a quick key for referencing:Ībsolute row, absolute column ($A$1:$B$2). Colloquially, these equate to "locking" selections on a particular set of cells, column, or row. To put these two sheets into context, here's our goal outcome.Ībsolute referencing: In this example, we'll be using absolute and relative referencing. But the search array can be much, much bigger. In this case, it'll be a list of states and their state codes. It contains the list of values you'll be searching for and the answer you want. Search array: This is like the answer key. NOTE: In many cases, you'll be working on a different worksheet than the output array. That's the column where we'll write the INDEX-MATCH-MATCH formula. In our example, we'll be using a short list of addresses. Worksheet: The worksheet is your working document or section of a worksheet. To accomplish an INDEX-MATCH-MATCH, you'll need a few parts. Parts of the INDEX-MATCH and INDEX-MATCH-MATCH If there are duplicates in your search array, INDEX-MATCH returns the value from the first instance, which might not be accurate. For a quick merge, the common value doesn't have to be as stringent as required for a primary key, but it should at the very least be unique. INDEX-MATCH is best used for merging data with unique identifiers, including:
![how to use vlookup in excel to match two columns how to use vlookup in excel to match two columns](https://i0.wp.com/basicexceltutorial.com/wp-content/uploads/2018/09/090318_2041_ExcelFormul7.png)
Like any data merge, INDEX-MATCH works best when you have a common, unique identifier between two data sources. When properly using referencing, you can pull a formula across a sheet and fill multiple columns. In other words, the array can be multiple columns. Unlike VLOOKUP, INDEX-MATCH can index multiple columns for fillable output. When writing a VLOOKUP formula, you have to manually enter the number of columns from the search column. INDEX-MATCH can look both left and right. The selection starts with the search column, and VLOOKUP only looks to the right of that. In other words, you have to initially select the both the search and output columns. A VLOOKUP array includes the lookup_array as well as the array.
![how to use vlookup in excel to match two columns how to use vlookup in excel to match two columns](https://www.webeera.com/wp-content/uploads/2021/09/how-to-use-vlookup-in-excel-to-compare-two-columns.png)
The biggest difference is that VLOOKUP only looks right. With both, you're telling Excel to find a value on a row and then return a value in a column beside it. The Difference Between VLOOKUP and INDEX-MATCH VLOOKUP and INDEX-MATCH operate a little differently though. Ultimately, you're trying to programmatically find information you don't know with information you do know. VLOOKUP and INDEX-MATCH does the exact same thing. Back in the olden days, you would open the phone book, flip through the pages to find a last name or business, and then move your finger over one or two columns to find the phone number. If you've never held a phone book, that's reasonable.
![how to use vlookup in excel to match two columns how to use vlookup in excel to match two columns](https://www.exceldemy.com/wp-content/uploads/2018/05/vlookup-formula-to-compare-two-columns-03.png)
It's like finding a phone number in a phone book. When Excel finds the search term it looks for the corresponding value on the same row in another column. With both VLOOKUP and INDEX-MATCH, you have a search term that you want to find in a column.