Search old worksheet for match then take date in next column and place in cell in new worksheet

[IainD]

OK let me see if I can explain what I am trying to do here.

I have two worksheets one with old data and one with new.

- New worksheet has additional rooms, but does not have a department column.
- Old worksheet does not have the new rooms, but has departments for each room.

For each individual room number on the new worksheet I want to query the entire range of room numbers on the old worksheet and where I find a match take the department in the next column cell on the old worksheet and move it to the column to the right of the room number of the new worksheet.

Can anyone suggest a method that may be able to achieve this?

 

[etaf]

use an index match or vlookup

assuming
old sheet is sheet2 and the room is in column A and dep in column b
and newsheet
column a is the room number
and dept in column b is required

=index(sheet2!B:B, match( a2, sheet2!a:a, 0))

where the room does not match you will get a
#N/A error

or

=vlookup(a2, sheet2!A;B, 2, false)