-1

How can I move values from Worksheet 1 column B to Worksheet 2 column B, based on a unique ID in column A? Some IDs are duplicates and some values are empty, I would like to ignore empty values.

Worksheet 1:

A      B
23452  value1
23452  
53252  value2
67452  value3

Worksheet 2:

A      B
53252
23452    
67452

Wanted Result (Worksheet 2):

A      B
53252  value2
23452  value1 
67452  value3

What I have tried

Merging data in Open/Libreoffice calc - could not figure this out for my specific requirements.

Tod
  • 160
  • 12

1 Answers1

1

You can try this simple INDEX-MATCH formula,

enter image description here

In column B of sheet2,

=INDEX('Sheet1'.A:B;MATCH(A1;'Sheet1'.A:A;0);2)
Gowtham Shiva
  • 3,802
  • 2
  • 11
  • 27
  • Thanks for the suggestion, but I am getting an error in OpenOffice `Error: Pair missing` - [Screenshot](https://i.gyazo.com/6ef0151accf19008e4831984bf2f4c28.png) – Tod Jun 07 '17 at 21:10
  • @Tod Looks like the delimiter should be `;` instead of `,`. Please try the updated formula – Gowtham Shiva Jun 08 '17 at 06:08
  • This now returns `#NAME?` I tried the comma delimited formula in Excel and it worked, but on larger amounts of data it just returns 0 in the first cell and nothing more. – Tod Jun 08 '17 at 06:22
  • @Tod Please update the screenshots of the error that you get to the question. Let me take a look – Gowtham Shiva Jun 08 '17 at 06:31
  • Data: [Screenshot A](http://i.gyazo.com/587fcfc5de146afa95ba62096865b59e.png) Formula: [Screenshot B](http://i.gyazo.com/5f232c2898cd1b0ef156512a09fb16a1.png) – Tod Jun 08 '17 at 06:45
  • @Tod Try this updated formula. If this is not working, then you may have to spend some time googling to find the equivalent of index-match and how to refer another sheet within a formula – Gowtham Shiva Jun 08 '17 at 14:49