How to align matching values in two columns in Excel and bring along associated values in other columns

Question

I want to know how to align values in one column with matching values in another column  as shown in How to align duplicates on the same rows in Excel  but also carry over associated values in other columns   I have four columns which show inventory item numbers and their associated values  Column A is a list of item numbers from the first month   Column B is the list of values for each item number in the first month   Column C is a list of item numbers from the second month -- which changes in number of rows and item numbers from the first month s list   Column D is the list of values for each item number in the second month  which may or may not change   Column A and Column C can be aligned by inserting a column in C  and using  IF ISNA MATCH A1 D D 0      INDEX D D MATCH A1 D D 0     This will align the like item numbers onto the same row  but will not align their respective values   How can this function be made to also bring along the associated values from Columns B and E  so that the final display shows the first month s item number and its value on the same row as the same second month s item number and its value  in four columns  all on the same row  so the difference in value can be calculated    Example   Col A Col B  Col C  Col D ITEM VALUE   ITEM   VALUE 11     11     11      9 12     15     12      16 13     13     15      21 14     8      17      24 15     12

User · Answer

assuming the item numbers are unique  a VLOOKUP should get you the information you need   first value would be  VLOOKUP E1 A B 2 FALSE   and the same type of formula to retrieve the second value would be  VLOOKUP E1 C D 2 FALSE   Wrap them in an IFERROR if you want to return anything other than  N A if there is no corresponding value in the item column s

User · Answer

Skip all of this   Download Microsoft FUZZY LOOKUP add in  Create tables using your columns  Create a new worksheet  INPUT tables into the tool  Click all corresponding columns check boxes   Use slider for exact matches  HIT go and wait for the magic

[excel] How to align matching values in two columns in Excel, and bring along associated values in other columns

Examples related to excel