# Sorting Common Values by Row in Excel

I have two sets of data from two years as below which needed to be sorted by product and row. Eg. I need A2 and its product sales on the same row for both years.I have had a look at the the solution here but don’t know how to adjust it.

``````    2012                 2011
Product   Sales     Product  Sales
A1          5       A1         3
A2          7       A4         5
A3          2       A6         3
A4          5       A7         2
A5          3       A9         4
A6          4       A10        5
A7          3
A8          4
A9          5
A10         6
``````

This is the desired result which has the same product on each row for both years.

``````    2012                 2011
Product   Sales     Product  Sales
A1          5       A1         3
A2          7
A3          2
A4          5       A4         5
A5          3
A6          4       A6         3
A7          3       A7         2
A8          4
A9          5       A9         4
A10         6       A10        5
``````

My actual database has over 800 rows over 8 years so performing this task manually is too time consuming. Is there any efficient way of doing this?

Help is much appreciated.

-
`vlookup` will do exactly this – Raystafarian Jul 16 '13 at 14:15

I would recommend you to use VLOOKUP function.

The syntax for vlookup is `VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)` where

• lookup_value is what you're trying to match, it can be a variable or a constant
• table_array is the table you are searching
• col_index_num is the column (starting with 1) of the table in which you are searching for the lookup_value
• range_lookup is whether you want it to return exact matches only; true or false

Fill H2 with the formula `=VLOOKUP(G2;A:B;2;0)` and copy until H11.

Fill I2 with the formula `=IFERROR(VLOOKUP(G2;C:D;2;0);0)` and copy until I11.

`IFERROR` is necessary because when the formula is looking for tha value `A2` for example it will not find it, so, we replace the error with `0`.

I think it is self explainable and make you able to figure out how to do this for the others columns.

-
Sorry for the multiple edits.. I believe my post is cohesive now. – Math Jul 16 '13 at 14:45
Hi, thanks for the reply. However, when I enter the formula into the cell I get an error. This is an image of the error. – LogiKal Jul 16 '13 at 15:54
Well, there's a configuration in Excel that determines the delimiter for more than one parameter in the formula, in my case is the semicolon (;), maybe for you is the comma (,). Switch the `;` by `,` and let me know if it works for you. – Math Jul 16 '13 at 17:11
Thanks for the help, it works now. – LogiKal Jul 16 '13 at 17:53