lookup formula

warhorse927

New Member
Joined
May 18, 2002
Messages
30
Two questions O guru's of computational,conversional,conversational spreadsheets!

If I have a 14 X 8 table filled with DATES. How do I display the date(s) that are between two dates? For instance between the dates 9/1/02 and 9/13/02 there are 3 dates on three different rows. Once finding those dates how can I get it to display the colunm head? TIA in humble humility (or should that be futility?)
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
On 2002-08-29 18:16, warhorse927 wrote:
Two questions O guru's of computational,conversional,conversational spreadsheets!

If I have a 14 X 8 table filled with DATES. How do I display the date(s) that are between two dates? For instance between the dates 9/1/02 and 9/13/02 there are 3 dates on three different rows. Once finding those dates how can I get it to display the colunm head? TIA in humble humility (or should that be futility?)

Most of that is fairly straightforward, but I'm not sure what you mean by displaying them in the column head.

Care to give a bit more info? an example would help, as might posting a snapshot of your sheet (using the html maker addin available from the link below this post).

Paddy
 

warhorse927

New Member
Joined
May 18, 2002
Messages
30
Sorry, I meant that ONCE the date in between two dates is found in the table, I would like to have a formula that would return the name of the column that the date is in.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
what happens if there's more than one instance of the date? if there is no instance?

Paddy
 

warhorse927

New Member
Joined
May 18, 2002
Messages
30

ADVERTISEMENT

Book1
ABCDEFGHIJ
2CohortABAM001ABAE002ABAE003ABAE004ABAE005ABAE006ABAE007ABAE008ABAE009
3#Students128691211121211
4
5IDS.1059/12/0010/23/001/18/013/13/015/7/016/14/017/25/018/21/019/18/01
6MGT.23010/3/0011/13/002/8/014/3/016/4/017/5/018/15/019/11/0110/9/01
7ENG.11111/7/001/1/013/15/015/8/017/9/018/9/019/19/0110/16/0111/13/01
8BUS.2401/2/012/5/014/26/016/12/018/13/019/20/0110/24/0111/27/011/1/02
9FAR.2202/6/013/12/015/31/017/24/019/24/0110/25/0112/5/011/15/022/5/02
10HIST.2023/13/014/16/017/12/018/28/0110/29/0112/6/011/23/022/19/023/12/02
11BUS.1454/17/015/28/018/16/0110/2/0112/3/011/24/022/27/024/2/024/23/02
12ENG.1126/5/017/16/0110/4/0111/27/012/4/023/14/024/24/025/21/026/11/02
13MGT.2507/17/018/20/0111/8/011/15/023/11/024/25/025/29/027/2/027/23/02
14BIO.2108/21/0110/1/011/3/022/19/024/22/025/30/027/10/028/6/028/27/02
15PHI.2119/25/0111/5/012/7/024/2/026/3/027/11/028/14/029/10/0210/8/02
16ENG.33011/6/0112/10/013/14/025/7/027/8/028/15/029/25/0210/22/0211/12/02
17THE.115.12/11/011/28/024/25/026/11/028/12/029/19/0210/30/0211/26/0212/31/02
18BUS.2601/29/023/4/025/30/027/23/029/23/0210/24/0212/11/021/14/032/4/03
19BUS.2503/5/024/15/027/11/028/27/0210/28/0212/5/021/29/032/18/033/11/03
20ECO.1014/16/025/27/028/15/0210/8/0212/2/021/23/033/5/033/25/034/22/03
21MKT.1205/28/027/1/029/19/0211/12/021/20/032/27/034/16/035/6/035/27/03
22MIS.1407/2/028/5/0210/24/0212/31/022/24/034/3/035/21/036/10/037/1/03
23ACC.1018/6/029/16/0212/5/022/4/033/31/035/15/037/2/037/15/038/5/03
24MGT.2049/10/0210/21/021/23/033/11/035/12/036/19/038/6/038/19/039/16/03
25BUS.29510/22/0211/25/022/27/034/22/036/23/037/31/039/10/039/30/0310/21/03
Sheet1


is this right? I can sum the students in a date range but I can't get the Cohort# to display based on the "in between" date(s)
 

warhorse927

New Member
Joined
May 18, 2002
Messages
30
OK, sorry I didn't finish my post. I want, on another spreadsheet, to have just the Cohort# in a cell based upon the date found.
There MIGHT be two dates on the same row or in the same column. If so, is there a way to display both Cohort#'s in separate cells?
TIA
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

If there 'might' be more than one match, the number of columns to return is variable, which makes it a little harder to set up a formula. However, I take it the, in principle, all columns might need to be retuurned, in which case why not do something like the following:
Book7
ABCDEFG
1ABCD
21/01/20028/01/200215/01/200222/01/2002
32/01/20029/01/200216/01/200223/01/2002
43/01/200210/01/200217/01/200224/01/2002
54/01/200211/01/200218/01/200225/01/2002
65/01/200212/01/200219/01/200226/01/2002
76/01/200213/01/200220/01/200227/01/2002
87/01/200214/01/200221/01/200228/01/2002
9
10
11FromToABCDColumns
121/01/20022/01/2002A   A
131/01/20029/01/2002AB  AB
14
15
16
Sheet2



use MCONCAT to get all the values in one cell - it's part of the morefunc addin available here:

http://longre.free.fr/english/index.html

paddy
 

warhorse927

New Member
Joined
May 18, 2002
Messages
30
Paddy, Sorry to take so long to get back to you. Your solution ALMOST is what I want. But the problem is I need the column head (cohort #) to be displayed if the following condition is met. IF a class falls between two dates (let's say Sept 1,2002 and sept. 30,2002) I need the cohort # to display. I have a spreadsheet with all the classes (column A) and a formula besides each class that sums the number of students that will take that class between the two dates. I need the Cohort#(s) to display beside that number. IF a class has a cohort starting between the dates, display cohort. I am stumped.
Warhorse
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
On 2002-09-04 11:07, warhorse927 wrote:
Paddy, Sorry to take so long to get back to you. Your solution ALMOST is what I want. But the problem is I need the column head (cohort #) to be displayed if the following condition is met. IF a class falls between two dates (let's say Sept 1,2002 and sept. 30,2002) I need the cohort # to display. I have a spreadsheet with all the classes (column A) and a formula besides each class that sums the number of students that will take that class between the two dates. I need the Cohort#(s) to display beside that number. IF a class has a cohort starting between the dates, display cohort. I am stumped.
Warhorse

That's what my suggestion does (I think!).

Problem
Columns of dates have column headings. If a date in any of the columns falls between arbitrary start & end dates, return the column heading. There may be one, more than one or no match.

Solution
Set up a seperate table which includes all the column headings & the start & end times. Devise a formula that will return a true / false for each column match. Concatenate all matched column headings into a single field.


For your situation, you might want to perform tha detailed calculations somewhere out of the way, then just return the concatenated results...

Post back with more info if you need - you might even be able to coerce me into looking at your spreadsheet.

Paddy
This message was edited by PaddyD on 2002-09-04 14:25
 

Forum statistics

Threads
1,148,220
Messages
5,745,456
Members
423,952
Latest member
EduardoM

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