Fetch multiple values based on criteria

[Lucero]

Hi, I have this data below as example and underneath the expected data output.
What's needed is a drop down box menu which allows user to choose an Agency and then to automatically populate in separate cells its respective Centers from "CentersList" and its respective IDs from the "IDList".

Is this something that can be done just with regular excel or does it need VBA? This is for my work and have been trying for two days, the drop down box I have shows 4 times the Agency name and I have no idea how to make it only once and for the Centers & its ID's to show in other cells.
I'm not that advanced in excel, but eager to learn. I'd appreciate ANY help anyone can give me! Thank you!

AgencyList	CentersList	IDList
ABC Agency	ABC Center 1	1231
ABC Agency	ABC Center 2	1232
ABC Agency	ABC Center 2	1233
ABC Agency	ABC Center 3	1234
BCC Agency	BCC Center 1	1235
BCC Agency	BCC Center 2	1236
BCC Agency	BCC Center 2	1237
BCC Agency	BCC Center 3	1238
BCC Agency	BCC Center 3	1239
CDD Agency	CDD Center 1	1240
CDD Agency	CDD Center 1	1241
CDD Agency	CDD Center 1	1242
CDD Agency	CDD Center 1	1243
CDD Agency	CDD Center 2	1244
CDD Agency	CDD Center 2	1245

This is how it should look:

Agency Name:	FROM DROP DOWN MENU ABC Agency
	ID:	Center Name:
	1231	ABC Center 1
	1231	ABC Center 2
	1233	ABC Center 3
	1234	ABC Center 4

by the way "ABC Center 2" is twice because there are actually two but each have a different ID.

[Admin]

Hi Lucero,

Welcome to ExcelFox !!

Try this http://www.excelfox.com/forum/f12/de...ight=dependent

[Rick Rothstein]

Except for the position of the columns, it sounds like AutoFilter may produce the lists you want to see. You didn't tell us the version of Excel that you are working on, so we cannot give you specific directions on how to find it, but you would start by selecting the Agency Name column and then turn on AutoFilter. Look for it either in the Data menu item of XL2003 or the Data tab on later versions of Excel.

[Lucero]

Except for the position of the columns, it sounds like AutoFilter may produce the lists you want to see. You didn't tell us the version of Excel that you are working on, so we cannot give you specific directions on how to find it, but you would start by selecting the Agency Name column and then turn on AutoFilter. Look for it either in the Data menu item of XL2003 or the Data tab on later versions of Excel.

Hi Rick, Im using excel 2007. Auto filter wouldn't work because the drop down menu is part of a budget template and the list of data needs to be hidden. Thank you though for your willingness to help me. Take care.

[Lucero]

Dear Admin, This is not working... What it's needed only one Drop Down box for the Agency and as soon as an agency is chosen, the Centers and ID's for that chosen agency should be populated in the cells below like the example. I'm sad I cannot figure it out and I'm not finding the right answer. Thank you though!! Take care

[Admin]

Hi,

Assume your data in A1:C16 row 1 being the header

Type the Agency name in G1

In G2 and copied down and across,

=IFERROR(INDEX(B$2:B$16,SMALL(IF($A$2:$A$16=$G$1,R OW(B$2:B$16)-ROW(B$2)+1),ROWS(G$2:G2))),"")

It's an array formula. Confirmed with CTRL + SHIFT + ENTER

[Lucero]

Dear Admin,
Thank you so much for your help!! The array formula is exactly what I needed!!! You Rock!!!

[Lucero]

Thank you so much for your help!! The array formula is exactly what I needed!!! You Rock!!! :D

[Admin]

Thanks for the feedback