Looking-up the values of one worksheet in another worksheet

Question

Basically, I could do this manually, but as the data is huge, it would take a lot of time. I am sure there must be a formulaic way of doing it.

I have two worksheets in Excel.

Worksheet 1.

Column-A        Column-B
Device Names    Total numbers
Device A           19 
Device B           81
Device C           12 
Device D           21 
etc.               etc.

Worksheet 2.

Column-A         Column-B
Device Names   Total numbers
Device D
Device S
Device Z
Device S
etc.

I need the formula which will look-up the Device names in Column-A/Worksheet 2 in ColumnA/Worksheet 1. If the device names in worksheet 2 and worksheet 1 match, the data in Column-B of worksheet-1 should be transferred to Column-B in worksheet-2.

Solution

Use the VLOOKUP() function.

In Column-B in Worksheet 2 for row 2 ("Device D"), I'd put something like:

=VLOOKUP(A1,Sheet1!A1:B4,2, FALSE)

A1 is the cell in Worksheet 2 that contains the value you're looking up (on Row 2, it's "Device D")

Sheet1!A1:B4 names the range in Worksheet 1 that contains the search table

2 is the column index in the search table, which basically calls out column B in Worksheet 1, as the value to return for when the device name (the first column) matches the value in Worksheet 2 for that row.

(Extremely common question by the way; this is not a special snowflake problem... You may have been able to easily google something similar.)

Comments

[barry houdini]

Agree with VLOOKUP but suggest you add FALSE as 4th argument for exact matches only, i.e. =VLOOKUP(A1,Sheet1!A$1:B$4,2,FALSE)

[David Zemens]

Agree with VLOOKUP and @barryhoudini . Add the FALSE argument in the VLOOKUP unless you are OK with approximate matches (for most users, most of the time you should add FALSE - otherwise it assumes the search table is organized in ascending order and returns the first value <= to the searched value)

[allquixotic]

Updated my answer. If you think the answer needs more explanation about the FALSE, feel free to propose an edit and I'll review it.