Looking-up the values of one worksheet in another worksheet
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 1:
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.)