0

I want to get some columns from one spreadsheet to another base on equability of columns. For example:

  • In the first sheet I have list of employees where column A is employee ID and column B is their name.

  • In the second sheet I have list of employees details where column A is employee ID and column B address and C is second address.

There might be employee IDs on the first spreadsheet that doesn't exists in the second spreadsheet and vice versa. I need the right formula to put in a new column in the first spreadsheet to fill it with the first/second employee's address based on the ID on the same line.

Hope I make sense.

Nir
  • 2,497
  • 9
  • 42
  • 71
  • It does make sense, and you'll need to look into `INDEX` and `MATCH` to retrieve those results. Have a look at this older post I made to try and simplify the procedure to set it up: [link](https://stackoverflow.com/a/58436658/9758194) – JvdV Dec 01 '19 at 15:38
  • Match return the wrong line `=match(A2,A:A)` return 67 which isn't like the `A2`. Maybe the '\' char in the string has anything to do with it? – Nir Dec 03 '19 at 10:45
  • The match returns the index of the value in the array you feed it. So if the value is found in the first element it returns 1 etc. Despite if it's on row 5. So then Index comes into play to return the correct result. – JvdV Dec 03 '19 at 11:00
  • The thing is that it gives wrong location, i.e the cells doesn't match. – Nir Dec 03 '19 at 13:15
  • Issue sorted by sorting the vector range. – Nir Dec 04 '19 at 14:47

1 Answers1

0

If you put this formula in cell C2 of your first sheet:

=LOOKUP(A2;Sheet!A:A;Sheet2!B:B)

Then the address (column B) should be returned. change B:B for C:C and it will return the second address.

To found out if the ID does exists in Sheet2 you can use this test:

=LOOKUP(A2;Sheet!A:A;Sheet2!A:A)=A2

The complete formula should be (to take into account non-existing values):

=IF(LOOKUP(A2;Sheet!A:A;Sheet2!A:A)=A2;=LOOKUP(A2;Sheet!A:A;Sheet2!B:B);"")
Luuk
  • 12,245
  • 5
  • 22
  • 33
  • It doesn't work. Even `=LOOKUP(A2;Sheet!A:A)` doesn't return the same value as `A2`. The values in the column contain many `\` in their name . Should it make any difference? – Nir Dec 03 '19 at 10:12
  • The editor didn't put the char right - should be '\'. For example values look like \asd4\asd3\asd2\asd1 – Nir Dec 03 '19 at 10:47
  • Issue sorted by sorting the vector range. – Nir Dec 04 '19 at 14:46
  • The function `LOOKUP` demans sorted lists. The function `VLOOKUP` can also be used with unsorted lists. [docs](https://support.office.com/en-us/article/lookup-function-446d94af-663b-451d-8251-369d5e3864cb) – Luuk Dec 07 '19 at 09:54