I have 2 excel sheets and both have one column that has matching data. However Sheet2 has a column of data that Sheet1 does not have and I need to input the corresponding data from Sheet2 into Sheet1 to complete it.

Match cell data in column B on Sheet2 to a cell in column B Sheet1.

Upon match, cell data from corresponding cell in column C Sheet2 inputs to column C in Sheet1.

Sheet 1:

System     Supplier Part Number      Company Part Number    Description
11120      V496-3-405                                       roller
11130      N554546                                          wheel
11140      N324560                                          bushing
11150      N145856                                          bearing
11160      N123456                                          castor
11170      4547876                                          spacer
11180      2000045                                          switch
etc        etc                                              etc

Sheet 2:

Company Part Number           Supplier Part Number
100235                        N123456
100500                        N145856
120050                        N324560
130500                        V496-3-405
160075                        2000045
170050                        4547876
180050                        N554546
etc                           etc
  • =INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0)) in C2 and auto-fill down will do it – Dirk Reichel Nov 12 '15 at 14:28
  • Your question doesn't match your example. – Raystafarian Nov 12 '15 at 18:12

You need to use the function VLOOKUP.

Syntax: VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

With Vlookup you can search for a match and bring the value in another cell of the same row of the match.

To make it work, you need to exchange columns on Sheet2 so it should look like this:

Supplier Part Number     Company Part Number
N123456                  100235
N145856                  100500
N324560                  120050
V496-3-405               130500
2000045                  160075
4547876                  170050
N554546                  180050
etc                      etc

Now, in Sheet1 cell C2you must put: =VLOOKUP(A2,Sheet2!A:B,2,FALSE).

It will look for a match of the value of A2 in the first column of the range Sheet2!A:B, and bring the content of column 2 of that range (Company Part Number).

FALSE means that it will look for the exact match.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.