10

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
Community
  • 1
  • 1
Scott Alexander
  • 101
  • 1
  • 1
  • 3
  • vlookup on the item number?? – John Bustos Oct 21 '13 at 17:13
  • That wasn't a very good title. If you want to refer to another question, please link to it from the body rather then putting a the name and post # in the title. I believe this is what you were trying to say. If I've interpreted the intent of the question incorrectly, please edit it to make it clearer. – Adi Inbar Oct 21 '13 at 17:25

2 Answers2

6

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.

4

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)

SeanC
  • 15,695
  • 5
  • 45
  • 66
  • can you please elaborate more.I have similar situation where i have three columns item1, value1, item2. how can i align same item1 and item2 in a row without losing value1 correspondingly. What would be the exact function? – Deepankar Singh Oct 07 '15 at 08:29
  • You could create a column (perhaps hidden) that concatenates the 3 columns into one for the purposes of the lookup. – David Parks Aug 29 '16 at 01:07
  • @DeepankarSingh You may use [single criteria index match](https://exceljet.net/formula/basic-index-match-exact), or with more matching requirements [multiple criteria index match](https://exceljet.net/formula/index-and-match-with-multiple-criteria) . – p._phidot_ Nov 25 '19 at 02:03