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

Anne Troy

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

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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,607
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,607
You know where you can put your data. :)

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,622
Messages
5,832,739
Members
430,160
Latest member
a_majda

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