Compare and insert......pleeeeeeease help :-)

**scottgzero** · 07-22-2014, 08:22 PM

Hi to everyone.....

I am a bit of a geek when it comes to Excel, but have some experience.

I have a query that is baffling me......maybe someone can help me and put me out of my misery :-)

I have two spreadsheets, both with 1 column containing the same data, but not sorted, and 1 column contains data that needs to be merged into the first spreadsheet.

Example :
Spreadsheet 1, Sorted and must stay in this sorting. So....fixed.
Column A1 : 12345
Column A2 : 34567
Column A3 : 56789
Column B1 : Empty
Column B2 : Empty
Column B3 : Empty

Spreadsheet 2 :
Column A1 : 56789
Column A2 : 34567
Column A3 : 12345
Column B1 : 50000
Column B2 : 60000
Column B3 : 70000

Result needed in Spreadsheet 1 :

Column A1 : 12345
Column A2 : 34567
Column A3 : 56789
Column B1 : 70000
Column B2 : 60000
Column B3 : 50000

So, I need it to match the two columns and give the 50000,60000,70000 etc Result.

I have seen some formulars, but these are written for those who understand the coding with ease :-(

Can anybody help me with a cell formular that is explained so I can understand it according to the example above?
I would be so greatful.

Many thanks

Adam

**gak67** · 07-22-2014, 09:11 PM

I can see three options (for all of these the formula would go in Sheet1!B1 and copied down).

Formula:

Please Login or Register  to view this content.

This formula looks for a value (in this case, whatever is in A1) in the first column of a specified range (Sheet2!$A$1:$B$3) and returns the corresponding entry from a subsequent column (in this case the 2nd column of the range). The 0 at the end (it stands for FALSE, and could be replaced by FALSE) tells it to find an exact match. It will return an error if it can't find an exact match.

Formula:

Please Login or Register  to view this content.

This formula first finds a match for a value within a range and returns the relative position of that value within that range. In this case the value in A1 (12345) is in the third row down in the Sheet2!$A$1:$A$3 range. It then moves that number of rows down another range (Sheet2!$B$1:$B$3) and returns the value in that cell.

Both of the above will find the first exact match in A1:A3, so you will need to keep that in mind if in your real data there are duplicates. These will also work with text rather than just numerical values.

The third option is

Formula:

Please Login or Register  to view this content.

This formula will sum anything in Sheet2!B1:B3 if the corresponding row in Sheet2!A1:A3 matches Sheet1!A1. If there are duplicates in column A it will add the column B values together, so it will only make sense for you if the values in column A are unique. The values in column B also need to be numeric so they can be summed.

**scottgzero** · 07-23-2014, 07:45 AM

You have indeed saved me so much hair tearing.

=INDEX(Sheet2!$B$1:$B$3,MATCH(Sheet1!A1,Sheet2!$A$1:$A$3,0))
Did it for me.
I saw this formular on another page, but could not get my head around it. You explained it fantastically for me and it all became clear.

Your help is much appreciated.

Thank you very very much.

Adam