Excel - Merge Tables with Index and Match Formulas
A lot of the times, I find myself having multiple Excel or CSV files with different tables & data that at some point, I need to merge them... Here is a not to myself! Use Index and Match formulas. Index Function The INDEX function returns a value or the reference to a value from within a table or range. Match Function The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range. Ex : INDEX ( return_range , MATCH ( lookup_value , lookup_range , 0)) =INDEX(Sheet1!$B$2:$B$218,MATCH($A2,Sheet1!$A$2:$A$218,0)) In this example, with Match, we're trying to find cell A2's exact value by looking in the "Sheet1", from A2 to A218. When the exact value of A2 is found in the range of Sheet1!$A$2:$A$218, with the Index ...