CODEWITHSUNDEEP
excel
Troy Valenta
Troy Valenta

Reputation: 23

Assign data from another sheet based on ID

I have two MS Excel sheets with data formatted as follows:

Sheet 1:

Num ID
1   A
2   D
3   B
4   A
5   A
6   C
7   D

Sheet 2:

ID  data1   data2
A   A1      A2
B   B1      B2
C   C1      C2
D   D1      D2
…   …       …

I'm trying to add values from sheet 2, columns data1 and data2 into sheet 1 such that the values for each row corresponds with the matching ID. E.g. 

Num ID  data1   data2
1   A   A1      A2
2   D   D1      D2
3   B   B1      B2
…   …   …       …

etc.

Thank you.

Upvotes: 1

Views: 76

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522254

You may use the VLOOKUP function twice here. First format your sheet 2 as follows:

  A   B      C   D
1 ID  data1  ID  data2
2 A   A1     A   A2
3 B   B1     B   B2
4 C   C1     C   C2
5 D   D1     D   D2
…   …       …

Here is your sheet 1 with labels:

  A   B
1 Num ID
2 1   A
3 2   D
4 3   B
5 4   A
6 5   A
7 6   C
8 7   D

Then enter the following formula into column c of sheet 1 for data1:

=VLOOKUP(B2, Sheet2!A2:B5, 2, FALSE)

Do the same also for data2:

=VLOOKUP(B2, Sheet2!C2:D5, 2, FALSE)

Upvotes: 1

Related Questions