Get excel columns where column x is in other spreadsheet

0
votes

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.

1
excelexcel-formulaspreadsheet
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 – JvdV
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
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
The thing is that it gives wrong location, i.e the cells doesn't match. – Nir
Issue sorted by sorting the vector range. – Nir

1 Answers

0
votes

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);"")