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

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,632
Office Version
  1. 365
Platform
  1. Windows
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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)
 
Upvote 0
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...]
 
Upvote 0
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
 
Upvote 0
Paddy,
Thank you. That works just fine. Not what I expected but I'll take it!!
Maybe we should name the XL guy Paddy?
 
Upvote 0
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
 
Upvote 0
You know where you can put your data. :)

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

Forum statistics

Threads
1,218,920
Messages
6,145,225
Members
450,602
Latest member
AceSpace

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