# Multiple Conditions with INDEX

#### jmacritz

##### New Member
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>

Last edited by a moderator:

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.

#### jmacritz

##### New Member
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

##### New Member
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

##### MrExcel MVP, Moderator

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

#### jmacritz

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

#### Fluff

##### MrExcel MVP, Moderator

You're welcome & thanks for the feedback

#### jmacritz

##### New Member
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

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

#### jmacritz

##### New Member
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.

Replies
0
Views
241

1,129,430
Messages
5,636,227
Members
416,908
Latest member
Streetsweeper

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back