Duplicate: Lookup, Vlookup, hlookup? Index? match? Offset?

[Anne Troy]

This can't be this hard. I have a table. Across the top, the column heads, I have Group names, such as ABAE 501 in column 1, ABAE 502 in Column 2, and so on.
the next row down contains the number of people in each group.
so:

Group l ABAE 501 l ABAE 502 l ABAE 503 l
size l 12 l 15 l 10 l

Eng. 111 l 9/12/02 l 9/24/02 l 9/16/02 l
Bus 240 l 10/10/02 l 11/28/02 l 10/21/02 l
MIS 310 l 11/14/02 l 01/05/02 l 12/02/02 l

there are 12 rows and 16 columns.

on another worksheet, I have a list of classes. I have the formula
Eng111 l{=SUM(IF(BookDelDate!$A3:$AA3>=Begdate,IF(BookDelDate!$A3:$AA3<=Enddate,students,0),0))} (students is the named range A1 to AA1)
Bus 240 l (formula with row 4)
MIS 310 l formula with row 5)
and on down

The data in the table consists of dates that show when a group will take a class. This formula accurately shows how many students will take a class within a date range. Begdate is cell D1 on the worksheet with the list of classes, Enddate is D2. A user can put in any beginning and any ending date and see how many students will take that class within the dates
I need something similar that will tell me which group they belong to! hope htis is clear enough.

_________________
Anne@MrExcel.com
This message was edited by Dreamboat on 2002-09-11 19:43

 

[PaddyD]

Anne,

I'm fiddling with something very similar for another board member at the moment - send the book over if you want & I'll have a look,

Paddy

(check your PM's)

 

[Anne Troy]

WOW. I work for the guy and even *I* have to log in again? I was just here a second ago!!

Anywaze, I don't have the file. Sorry!!



[It's really posted at another board that doesn't specialize only in Excel...]

 

[Anne Troy]

Ah!

I have just learned through my underground sources that this question already exists!

http://www.mrexcel.com/board/viewtopic.php?topic=21759&forum=2

 

[PaddyD]

OK - as the OP indicated, it is straightforward to count the number of students. The difficulty lies in returning the column headings. Case by case this is easy - the difficulty arrises because you don't know how many column headings you'll need to return in advance - it will depend on the various dates. It is possible to deal with this uncertainty in a single step with the use of addin functions (specifically by using MCONCAT to return the result of an array where the array in this case is the list of matched column headings). However, it is easier to (a) assume that only native excel functions are available, and (b) doing it in a couple of steps is OK.

So

Problem
Return as a single string the column headings for columns of data where dates in the corresponding columns are between arbitrary start and end dates.

Solution
1) Create a secondary table to return the column names when the condition is met.
2) Concatenate the results into a single value.

See the simulation:

Book7
	A	B	C	D	E	F
1	Cohort	ABAE501	ABAE502	ABAE503
2	Size	12	15	10
3	ENG111	12Sep02	24Sep02	16Sep02
4	BUS240	10Oct02	28Nov02	21Oct02
5	MIS310	14Nov02	05Jan02	02Dec02
6
7
8	BegDate	10Oct02
9	EndDate	15Nov02
10
11
12		Counts	ABAE501	ABAE502	ABAE503	Cohorts
13	ENG111	0
14	BUS240	22	ABAE501		ABAE503	ABAE501ABAE503
15	MIS310	12	ABAE501			ABAE501
16
17
Sheet3

The 'secondary table' is the area in grey. It could be moved to an out of the way area if wanted.

Paddy
This message was edited by PaddyD on 2002-09-11 19:59

 

[warhorse927]

Paddy,
Thank you. That works just fine. Not what I expected but I'll take it!!
Maybe we should name the XL guy Paddy?

 

[PaddyD]

With thanks to Aladin...

As you have morefunc, this is do-able in a single step with a formula of the form:

=MCONCAT(IF((B2:E2>=B4)*(B2:E2<=B5),B1:E1&","," "),"")

(array entered)

Book6
	A	B	C	D	E
1		A	B	C	D
2		1/01/2002	2/02/2002	3/01/2002	4/02/2002
3
4	Start	1/01/2002
5	End	31/01/2002
6	MatchedColumns	A,C,
7
8
Sheet1

Paddy

 

[Anne Troy]

XL guys new name:

"Paddy'd Cell"

 

[Mark W.]

Anne, you really should consider putting your data in 1st Normal Form (1NF).

 

[Anne Troy]

You know where you can put your data.

It's not my file. It's Warhorses... I was just tryin' ta help!!