Multiple Conditions with INDEX

jmacritz

New Member
Joined
May 9, 2019
Messages
10
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
RoomDateResources
TimOak9-may-19Projector
TonyPine7-may-19Fold Out
MikePine15-may-19Laptop
TimElm10-may-19Sci Equip
LucyElm13-may-19Projector
LucyOak14-may-19Laptop
MikeWillow5-may1-9Fold Out
SandraPine22-may-19Projector
VictoriaWillow28-may-19Projector

<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
Joined
Jun 12, 2014
Messages
56,495
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Pine</td><td style=";">Mike</td><td style="text-align: right;;">15/05/2019</td><td style=";">Laptop</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style=";">Sandra</td><td style="text-align: right;;">22/05/2019</td><td style=";">Projector</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">Sheet1!$A$2:$A$10,AGGREGATE(<font color="Green">15,6,(<font color="Purple">(<font color="Teal">ROW(<font color="#FF00FF">Sheet1!$B$2:$B$10</font>)-ROW(<font color="#FF00FF">Sheet1!$B$2</font>)+1</font>)</font>)/(<font color="Purple">(<font color="Teal">Sheet1!$B$2:$B$10=$B$2</font>)*(<font color="Teal">Sheet1!$C$2:$C$10<=TODAY(<font color="#FF00FF"></font>)+14</font>)*(<font color="Teal">Sheet1!$C$2:$C$10>=TODAY(<font color="#FF00FF"></font>)</font>)</font>),ROWS(<font color="Purple">$A$1:A1</font>)</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">Sheet1!C$2:C$10,AGGREGATE(<font color="Green">15,6,(<font color="Purple">(<font color="Teal">ROW(<font color="#FF00FF">Sheet1!$B$2:$B$10</font>)-ROW(<font color="#FF00FF">Sheet1!$B$2</font>)+1</font>)</font>)/(<font color="Purple">(<font color="Teal">Sheet1!$B$2:$B$10=$B$2</font>)*(<font color="Teal">Sheet1!$C$2:$C$10<=TODAY(<font color="#FF00FF"></font>)+14</font>)*(<font color="Teal">Sheet1!$C$2:$C$10>=TODAY(<font color="#FF00FF"></font>)</font>)</font>),ROWS(<font color="Purple">$A$1:B1</font>)</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">Sheet1!D$2:D$10,AGGREGATE(<font color="Green">15,6,(<font color="Purple">(<font color="Teal">ROW(<font color="#FF00FF">Sheet1!$B$2:$B$10</font>)-ROW(<font color="#FF00FF">Sheet1!$B$2</font>)+1</font>)</font>)/(<font color="Purple">(<font color="Teal">Sheet1!$B$2:$B$10=$B$2</font>)*(<font color="Teal">Sheet1!$C$2:$C$10<=TODAY(<font color="#FF00FF"></font>)+14</font>)*(<font color="Teal">Sheet1!$C$2:$C$10>=TODAY(<font color="#FF00FF"></font>)</font>)</font>),ROWS(<font color="Purple">$A$1:C1</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

jmacritz

New Member
Joined
May 9, 2019
Messages
10
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
Joined
May 9, 2019
Messages
10
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
Joined
Jun 12, 2014
Messages
56,495
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

jmacritz

New Member
Joined
May 9, 2019
Messages
10
The formula works perfectly thank you. And it's a lot cleaner than the one I was using.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,495
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome & thanks for the feedback
 

jmacritz

New Member
Joined
May 9, 2019
Messages
10
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
Joined
Jun 12, 2014
Messages
56,495
Office Version
  1. 365
Platform
  1. Windows
Is Bookings the sheet name, or the name of a table?
Also could you post one of the modified formulae?
 

jmacritz

New Member
Joined
May 9, 2019
Messages
10
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top