+ Reply to Thread
Results 1 to 7 of 7

If date matches and room matches, return room info in to cell

  1. #1
    Registered User
    Join Date
    03-12-2018
    Location
    Scotland
    MS-Off Ver
    2010
    Posts
    3

    Unhappy If date matches and room matches, return room info in to cell

    Hello,

    I have a spreadsheet with 2 sheets.

    Sheet 1 has dates down one column (B), and room numbers across a row(2).

    Sheet 2 has dates for meetings in column A, room number it is in in column K, and the data about the meeting in Column L.

    I want to have the meeting data(L) to be filled in if the date and room number match.
    I can do this if I use;

    =IF(AND($B10=Sheet2!A3,Sheet2!$K3=Sheet1!E2),Sheet2!L3,"")

    but I can not change 'Sheet2!A3' to be 'if anywhere in this range'.

    I think I might be going about this wrong?!

    Thank you,

    Kerry

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: If date matches and room matches, return room info in to cell

    Try this ...

    =IFERROR(LOOKUP(2,1/($B10=Sheet2!$A$3:$A$300,E$2=Sheet2!$K$3:$K$300),Sheet2!$L$3:$L$300),"")

  3. #3
    Registered User
    Join Date
    03-12-2018
    Location
    Scotland
    MS-Off Ver
    2010
    Posts
    3

    Re: If date matches and room matches, return room info in to cell

    Hi Phuocam,

    That doesn't appear to be working although I assume my explanation skills are the problem! Attached is a image showing the 2 sheets side by side, which I'd imagine explains it a lot better than I can!

    Diary test.png

    Many, many thanks!

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: If date matches and room matches, return room info in to cell

    Try in Cell C3:

    =IFERROR(LOOKUP(2,1/($B3=Sheet2!$A$3:$A$300,C$2=Sheet2!$K$3:$K$300),Sheet2!$L$3:$L$300),"")

    Copy to right and down.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,991

    Re: If date matches and room matches, return room info in to cell

    Try

    in C3

    =IFERROR(LOOKUP(2,1/($B3=Sheet2!$A$3:$A$300)/(D$2=Sheet2!$K$3:$K$300),Sheet2!$L$2:$L$300),"")

    Copy across and down

  6. #6
    Registered User
    Join Date
    03-12-2018
    Location
    Scotland
    MS-Off Ver
    2010
    Posts
    3

    Re: If date matches and room matches, return room info in to cell

    Thank you so much!

    The answer was;

    =IFERROR(LOOKUP(2,1/($B3=Sheet2!$A$2:$A$300)/(C$2=Sheet2!$K$2:$K$300),Sheet2!$L$2:$L$300),"")

    Now just to find out what to do when there is 2 things booked in to a room.

    I think that might be a problem for another day however!

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,991

    Re: If date matches and room matches, return room info in to cell

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to Calculate Hotel room nights booked, for each night, and broken down by room type?
    By salsadantzr in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-09-2018, 04:53 PM
  2. Room Allotment:remove allotted room from dropdown list
    By amdrosm in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-05-2017, 04:50 AM
  3. Converting tabular data into room-by-room
    By tomca in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-03-2016, 07:00 AM
  4. Replies: 9
    Last Post: 10-08-2015, 01:50 PM
  5. [SOLVED] Need help with Index IF Match Formula. Returning Room Number that Matches the Hours Used
    By KattieSpencer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2014, 12:22 PM
  6. Replies: 3
    Last Post: 02-27-2007, 05:27 AM
  7. [SOLVED] How do I set up a 52 week agenda with day ,date and room to write
    By Richard in forum Excel General
    Replies: 1
    Last Post: 09-10-2005, 12:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1