help! stuck! If, Index, Small, Row, Rows!

S

sandy12345

New Member
Joined
Mar 9, 2015
Messages
5
[TABLE="width: 511"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
first row is building name, next row is dept #, next row is area of study. The dates to the right are all of the dates that a meeting is scheduled. I am trying to search a specific date and return a result that lists the bldgs, dept, area for that specific date.

Below my chart is a drop down which i would choose one of the dates. I also have a cell next to this stating how many occur on the date I chose. I have been trying to two days to figure out how to list what I show below in Box A. The closest I have come is:

=IF(ROWS(Q$14:Q15)>$S$2,"",INDEX(D$2:D$12,SMALL(IF($C$2:$C$12=$R$2,ROW($C$2:$C$12)-1),ROWS(Q$14:Q15)))), which of course is listing the logistics of my table that I am using. This returns me three rows of data from the first column of dates (DATE 1) but I am unable to figure out how to add another column of dates (DATE 2 and DATE 3) to add to the results. Any help would be immensely appreciated.

BLDG DEPT AREA DATE 1 DATE 2 DATE 3
[TABLE="width: 511"]
<tbody>[TR]
[TD]Davis[/TD]
[TD]A104[/TD]
[TD]Env studies[/TD]
[TD="align: right"]6/1/16[/TD]
[TD="align: right"]6/1/17[/TD]
[TD="align: right"]6/1/18[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Cornell[/TD]
[TD]A107[/TD]
[TD]Vet medicine[/TD]
[TD="align: right"]3/1/17[/TD]
[TD="align: right"]3/1/18[/TD]
[TD="align: right"]3/1/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]UC Berkeley[/TD]
[TD]A110[/TD]
[TD]Pol Science[/TD]
[TD="align: right"]7/1/17[/TD]
[TD="align: right"]7/1/18[/TD]
[TD="align: right"]7/1/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Stanford[/TD]
[TD]A111[/TD]
[TD]Med Research[/TD]
[TD="align: right"]6/1/16[/TD]
[TD="align: right"]6/1/17[/TD]
[TD="align: right"]6/1/18[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]U of Colorado[/TD]
[TD]A112A[/TD]
[TD]Technology[/TD]
[TD="align: right"]10/1/16[/TD]
[TD="align: right"]10/1/17[/TD]
[TD="align: right"]10/1/18[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]U of Texas[/TD]
[TD]B137[/TD]
[TD]Env studies[/TD]
[TD="align: right"]6/1/16[/TD]
[TD="align: right"]6/1/17[/TD]
[TD="align: right"]6/1/18[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]U of Arizona[/TD]
[TD]B140[/TD]
[TD]Accounting[/TD]
[TD="align: right"]11/1/17[/TD]
[TD="align: right"]11/1/18[/TD]
[TD="align: right"]11/1/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Umass[/TD]
[TD]B143[/TD]
[TD]Business[/TD]
[TD="align: right"]10/1/16[/TD]
[TD="align: right"]10/1/17[/TD]
[TD="align: right"]10/1/18[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]UC Irvine[/TD]
[TD]B144A[/TD]
[TD]Computer Science[/TD]
[TD="align: right"]3/1/17[/TD]
[TD="align: right"]3/1/18[/TD]
[TD="align: right"]3/1/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]UC Santa Barbara[/TD]
[TD]B148[/TD]
[TD]Biology[/TD]
[TD="align: right"]7/1/17[/TD]
[TD="align: right"]7/1/18[/TD]
[TD="align: right"]7/1/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]UC Santa Cruz[/TD]
[TD]B128[/TD]
[TD]Web design[/TD]
[TD="align: right"]6/1/16[/TD]
[TD="align: right"]6/1/17[/TD]
[TD="align: right"]6/1/18[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 251"]
<tbody>[TR]
[TD]Table B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]date to search[/TD]
[TD="colspan: 2"]number of dates in table[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]
6/1/16​
[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]results:[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Davis[/TD]
[TD]A104[/TD]
[TD]Env studies[/TD]
[/TR]
[TR]
[TD]Stanford[/TD]
[TD]A111[/TD]
[TD]Med Research[/TD]
[/TR]
[TR]
[TD]U of Texas[/TD]
[TD]B137[/TD]
[TD]Env studies[/TD]
[/TR]
[TR]
[TD]UC Santa Cruz[/TD]
[TD]B128[/TD]
[TD]Web design[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
I think I understand what you are asking, but I don't understand what the formula you have is doing...
What is in Cells: R2 and S2
And is the expected results actually just under the table, or on another tab...?
Before jumping into trying to figure it out, would conditional formatting be good enough for you...?
i.e. ( If any of the cells in the range match the date, then highlight that row..or something like that...)
 
Upvote 0
One way, that keeps the formulas fairly simple, would be to use a helper column, which could be hidden once populated.

J1 is blank or houses a 0
J2 copied down to the end of the data (at least)
A18 copied across to C18 and down as far as you might ever need


Excel Workbook
ABCDEFGHIJ
1BLDGDEPTAREADATE 1DATE 2DATE 3*
2DavisA104Env studies6/01/166/01/176/01/181
3CornellA107Vet medicine3/01/173/01/183/01/191
4UC BerkeleyA110Pol Science7/01/177/01/187/01/191
5StanfordA111Med Research6/01/166/01/176/01/182
6U of ColoradoA112ATechnology10/01/1610/01/1710/01/182
7U of TexasB137Env studies6/01/166/01/176/01/183
8U of ArizonaB140Accounting11/01/1711/01/1811/01/193
9UmassB143Business10/01/1610/01/1710/01/183
10UC IrvineB144AComputer Science3/01/173/01/183/01/193
11UC Santa BarbaraB148Biology7/01/177/01/187/01/193
12UC Santa CruzB128Web design6/01/166/01/176/01/184
134
14
15Date to search
166/01/16
17
18DavisA104Env studies
19StanfordA111Med Research
20U of TexasB137Env studies
21UC Santa CruzB128Web design
22
23
Make List
 
Upvote 0
Thank you Peter! I can't believe I have been working on this for 2 days and you solved it in a minute probably. Sad part is I don't think i'm much better at Excel than 2 days ago. I don't know how you came up with the helper cells and the formula but thank you so much!

[TABLE="width: 678"]
<colgroup><col><col><col><col span="6"><col></colgroup><tbody>[TR]
[TD]Building[/TD]
[TD]Dept#[/TD]
[TD]Area of study[/TD]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Date 3[/TD]
[TD]Date 4[/TD]
[TD] [/TD]
[TD="colspan: 2"]Spreadsheet Formulas[/TD]
[/TR]
[TR]
[TD]Davis[/TD]
[TD]A104[/TD]
[TD]Env studies[/TD]
[TD="align: right"]6/1/16[/TD]
[TD="align: right"]6/1/17[/TD]
[TD="align: right"]6/1/18[/TD]
[TD="align: right"]6/1/19[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Cornell[/TD]
[TD]A107[/TD]
[TD]Vet medicine[/TD]
[TD="align: right"]3/1/17[/TD]
[TD="align: right"]3/1/18[/TD]
[TD="align: right"]3/1/19[/TD]
[TD="align: right"]2/1/20[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]UC Berkeley[/TD]
[TD]A110[/TD]
[TD]Pol Science[/TD]
[TD="align: right"]7/1/17[/TD]
[TD="align: right"]7/1/18[/TD]
[TD="align: right"]7/1/19[/TD]
[TD="align: right"]6/1/20[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Stanford[/TD]
[TD]A111[/TD]
[TD]Med Research[/TD]
[TD="align: right"]6/1/16[/TD]
[TD="align: right"]6/1/17[/TD]
[TD="align: right"]6/1/18[/TD]
[TD="align: right"]6/1/19[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]U of Colorado[/TD]
[TD]A112A[/TD]
[TD]Technology[/TD]
[TD="align: right"]10/1/16[/TD]
[TD="align: right"]10/1/17[/TD]
[TD="align: right"]10/1/18[/TD]
[TD="align: right"]10/1/19[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]U of Texas[/TD]
[TD]B137[/TD]
[TD]Env studies[/TD]
[TD="align: right"]6/1/16[/TD]
[TD="align: right"]6/1/17[/TD]
[TD="align: right"]6/1/18[/TD]
[TD="align: right"]6/1/19[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]U of Arizona[/TD]
[TD]B140[/TD]
[TD]Accounting[/TD]
[TD="align: right"]11/1/17[/TD]
[TD="align: right"]11/1/18[/TD]
[TD="align: right"]11/1/19[/TD]
[TD="align: right"]10/1/20[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Umass[/TD]
[TD]B143[/TD]
[TD]Business[/TD]
[TD="align: right"]10/1/16[/TD]
[TD="align: right"]10/1/17[/TD]
[TD="align: right"]10/1/18[/TD]
[TD="align: right"]10/1/19[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]UC Irvine[/TD]
[TD]B144A[/TD]
[TD]Computer Science[/TD]
[TD="align: right"]3/1/17[/TD]
[TD="align: right"]3/1/18[/TD]
[TD="align: right"]3/1/19[/TD]
[TD="align: right"]2/1/20[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]UC Santa Barbara[/TD]
[TD]B148[/TD]
[TD]Biology[/TD]
[TD="align: right"]3/1/17[/TD]
[TD="align: right"]7/1/18[/TD]
[TD="align: right"]7/1/19[/TD]
[TD="align: right"]6/1/20[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]UC Santa Cruz[/TD]
[TD]B128[/TD]
[TD]Web design[/TD]
[TD="align: right"]6/1/16[/TD]
[TD="align: right"]6/1/17[/TD]
[TD="align: right"]6/1/18[/TD]
[TD="align: right"]6/1/19[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]date to search[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]6/1/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Davis[/TD]
[TD]A104[/TD]
[TD]Env studies[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Stanford[/TD]
[TD]A111[/TD]
[TD]Med Research[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]U of Texas[/TD]
[TD]B137[/TD]
[TD]Env studies[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]UC Santa Cruz[/TD]
[TD]B128[/TD]
[TD]Web design[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Glad it worked for you. Thanks for letting us know.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Best Solution for returning data - index?
Replies
5
Views
183
Peter_SSs
Peter_SSs
H
  • Solved
First cell with a value of 0 after a certain row
2
Replies
10
Views
248
Fluff
Fluff
F
  • Question Question
Changing date format based on fiscal years versus calendar years
Replies
2
Views
164
Peter_SSs
Peter_SSs
G
  • Solved
If Else Loop macro running slow, anyway to speed it up?
Replies
9
Views
258
Alex Blakenburg
A
D
  • Question Question
Assign dates as late as possible without duplicates
Replies
1
Views
140
AlphaFrog
AlphaFrog

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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
Back
Top