Lost in VLOOKUP, OFFSET, INDEX, etc...

[JefferyK]

Dear users,

First time I'm posting a question here.
Normally, with some searching, I find a solution or a work around, however this time I'm stuck.

I'm trying to fetch data from another worksheet based on a cell value (room number). The sheet, from which I'm trying to fetch the data, contains the data for multiple room. The data is in various columns.

So I want to lookup the room number in the other sheet, and then fetch the data which is in a fixed position (row/column) in comparison to the room number.

Does this make any sense?

VLOOKUP/HLOOKUP does not work because the data is not in the same row/column of the refence (room no.)
OFFSET does not work because the reference is variable.

Hope somebody can help me!

Best regards,
Jeffery