Lookup formula to match a value in a range of multiple columns of another sheet

Hi! Im trying to use a lookup formula. I want to look up Guestlist worksheet column D to Rooms worksheet columns E to I and then output the value of Rooms worksheet column D to Guestlist worksheet column E. In summary, I want to look for the persons name in the guest list to the rooms worksheet and take the room number assigned to that person and output it in the guest list. Pls see attachment.

Thank you!

I look at the file and i don't understand what exactly do you want as a result? Maybe you should manually type the results in your excel file to be more clear

Hi Azumi. I want to look up guestlist column D (fullname), to Rooms sheet columns (E-I) Guest 1 to 5, and then get the corresponding Rooms sheet column D (Room #) and place it at Room# column E of the Guestlist sheet.

Hi JeteMc, THANK YOU very much! This formula does the trick.