Multiple Conditions with INDEX

[jmacritz]

I have a table on one sheet called BOOKINGS that has information for who booked a room, what room they booked, the date and what resources they need. I can look up and display every entry for a particular room easily enough but what I'd like to do is to display only the entries for that room within the next two weeks. An example of my table

On a different sheet I'd like have the user input the room name (say in cell b2) and have it display Booked By, Date, Resources for anything matching the room name and within the next two weeks Any help would be appreciated

<tbody>
[TD="class: xl66"]

[TABLE="width: 500"] <tbody>[TR] [TD]Booked By	Room	Date	Resources
Tim	Oak	9-may-19	Projector
Tony	Pine	7-may-19	Fold Out
Mike	Pine	15-may-19	Laptop
Tim	Elm	10-may-19	Sci Equip
Lucy	Elm	13-may-19	Projector
Lucy	Oak	14-may-19	Laptop
Mike	Willow	5-may1-9	Fold Out
Sandra	Pine	22-may-19	Projector
Victoria	Willow	28-may-19	Projector

<tbody>
</tbody>

[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to MrExcel.
How about

Excel 2013/2016
	B	C	D	E
2	Pine	Mike	15/05/2019	Laptop
3		Sandra	22/05/2019	Projector
Sheet2

Cell Formulas
Range	Formula
C2	=IFERROR(INDEX(Sheet1!$A$2:$A$10,AGGREGATE(15,6,((ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1))/((Sheet1!$B$2:$B$10=$B$2)*(Sheet1!$C$2:$C$10<=TODAY()+14)*(Sheet1!$C$2:$C$10>=TODAY())),ROWS($A$1:A1))),"")
D2	=IFERROR(INDEX(Sheet1!C$2:C$10,AGGREGATE(15,6,((ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1))/((Sheet1!$B$2:$B$10=$B$2)*(Sheet1!$C$2:$C$10<=TODAY()+14)*(Sheet1!$C$2:$C$10>=TODAY())),ROWS($A$1:B1))),"")
E2	=IFERROR(INDEX(Sheet1!D$2:D$10,AGGREGATE(15,6,((ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1))/((Sheet1!$B$2:$B$10=$B$2)*(Sheet1!$C$2:$C$10<=TODAY()+14)*(Sheet1!$C$2:$C$10>=TODAY())),ROWS($A$1:C1))),"")

 

[jmacritz]

Thanks for the quick reply. I can follow most of the formula but I'm not familiar with the AGGREGATE function. I'll give it a try and see how it works out for me.
Currently what I'm using to display all the data on a given room is

=IFERROR(INDEX('BOOKINGS'!$A$2:$D$10, SMALL(IF(($B$2='BOOKINGS'!$B$2:$B$10), ROW('BOOKINGS'!$B$2:$B$10)-MIN(ROW('BOOKINGS'!$B$2:$B$10))+1," "),ROW('BOOKINGS'!A1)),COLUMN('BOOKINGS'!A1)),"")

 

[jmacritz]

Since I can't edit my post - I looked up the AGGREGATE function and correct me if I'm wrong but with the setting of 15,6 its basically doing the same thing as my SMALL function and ignoring the errors, correct?

 

[Fluff]

That's right, but using Aggregate means it's a "normal" formula, rather than an Array formula

 

[jmacritz]

The formula works perfectly thank you. And it's a lot cleaner than the one I was using.

 

[Fluff]

You're welcome & thanks for the feedback

 

[jmacritz]

When I used the formula on my actual table, it displays the first row perfectly fine, but everything else is blank. The only difference is the range (from 10 to 59, which I changed) and the sheet name I changed it to my table (Sheet1 to 'BOOKINGS') can I not reference the table with this?

 

[Fluff]

Is Bookings the sheet name, or the name of a table?
Also could you post one of the modified formulae?

 

[jmacritz]

I renamed the Sheet to Booking Data. The range changed from 2:10 to 2:59.
=IFERROR(INDEX('Booking Data'!$A$2:$A$59,AGGREGATE(15,6,((ROW('Booking Data'!$B$2:$B$59)-ROW('Booking Data'!$B$2)+1))/(('Booking Data'!$B$2:$B$59=$B$2)*('Booking Data'!$C$2:$C$59<=TODAY()+14)*('Booking Data'!$C$2:$C$59>=TODAY())),ROWS($A$1:A1))),"")

The first record shows without a problem but every other record is blank.