How to use vlookup in Excel 2010 for serial numbers?

Question

Ok I have two workbooks in one spreadsheet. One workbook has serial numbers with the warranty dates. The other workbook has serial numbers with names. How would I go about combining the two so that I have serial, warranty and names on one sheet?? I know I would use a vlookup but I am unsure about what values go where. Can someone assist me?
For the first workbook with serial and warranty- Serials are column A; Warranty are Column E
For the second workbook with serial and names- Serials are column D; Names are Column A

Please assist me in combining these.

Answer 1

Sheet 2

E2 =INDEX(Sht1!E:E, MATCH($D2,Sht1!A:A,0))

Answer 2

So you just want a sheet with ALL three information on it. So I would use either of the sheets and just add the missing information with vlookup.



I'd go with the sheet with the Serials & Warranties - you already have 2/3 of what you want, so we just need to pull the names from the 2nd sheet.



Let's say you want the Names in Cell F2 (in the 1st Workbook):



1) In the "2nd Workbook" you have to put the Serials in Column A because only the values in the first column of table_array are the values searched by lookup_value and we want to use the Serials for our formula.



(Select Column D, right click, select "Cut" and then right click column A and select "Insert Cut Cells") With this done, in the 2nd Workbook, you should now have your Serials in Column A and Names in Column B.



2) Put the following formula in cell F2 in your 1st Workbook (I'm assuming you have headings). In this formula, I am assuming the 2nd workbook is actually named "2nd Workbook" and that you have headers and are starting the data at A2



=VLOOKUP(A2, '2nd Workbook'!A$2:D$1000, 2, FALSE)



This formula should pull the name that matches the serial number in Column A. You can then just either copy and paste the formula all the way down or drag it.



Let me know if you need anything else. (:

Answer 3

1

Answer 4

Here's a video showing you how to do a Vlookup in Excel 2007 (it is also valid for Excel 2010):

https://www.youtube.com/watch?v=g2YlBT7fiuk