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

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> 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> </tbody>

<tbody>
</tbody>

 

[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.