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
This question is related to
excel
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.
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)
Source: Stackoverflow.com