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

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,603
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,603
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...]
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

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
ABCDEF
1CohortABAE501ABAE502ABAE503
2Size121510
3ENG11112Sep0224Sep0216Sep02
4BUS24010Oct0228Nov0221Oct02
5MIS31014Nov0205Jan0202Dec02
6
7
8BegDate10Oct02
9EndDate15Nov02
10
11
12CountsABAE501ABAE502ABAE503Cohorts
13ENG1110    
14BUS24022ABAE501 ABAE503ABAE501ABAE503
15MIS31012ABAE501  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

New Member
Joined
May 18, 2002
Messages
30
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

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
ABCDE
1ABCD
21/01/20022/02/20023/01/20024/02/2002
3
4Start1/01/2002
5End31/01/2002
6MatchedColumnsA,C,
7
8
Sheet1


Paddy
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Anne, you really should consider putting your data in 1st Normal Form (1NF).
 

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,603
You know where you can put your data. :)

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

Watch MrExcel Video

Forum statistics

Threads
1,119,264
Messages
5,577,081
Members
412,763
Latest member
sienweiw
Top