return multiple items from one lookup

[chavez000]

Hello All,

Having trouble with my formula for returning multiple items from a table based on a criteria. Please see attached sample. any help is appreciated.

[Ace_XL]

Try

In E5
=IF(ROWS($A$1:$A1)>COUNTIF(Table1[Region],$D$3),"",INDEX(Table1[City],SMALL(INDEX((Table1[Region]=$D$3)*(ROW(Table1[Region])-ROW($L$5)+1),),COUNTIF(Table1[Region],"<>"&$D$3)+ROWS($A$1:$A1))))

In F5
=IF(E5="","",INDEX(Table1[Budget],MATCH(E5,Table1[City],0)))

Copy down

[chavez000]

Genius; thank you Ace!

[chavez000]

Hi Ace,

You were kind enough to help me out with the above formula a couple of weeks ago. I need your assistance expanding the formula to add one additional criteria - year. The year is in a drop down list. The formula above works perfect, but i have the same cities listed several times in the table with the budgets updated for future years. I need the formula to recognize the region AND year from two different drop down lists. Any help is appreciated.

[AliGW]

The formula in E5 is not the one you were given. If you do as instructed in post #2, you will get this:

Excel 2016 (Windows) 32 bit

	E	F
4	City	Budget
5	Buena Vista	346000
6	Carson City	28900
7	Vermont	2367

Sheet: Table

[chavez000]

Hi AliGW,

You are correct, i uploaded the incorrect sheet. However, i am now having trouble with the sheet i initially had working. It is not returning the correct string of cities using the above formula (im sure i a missing something silly). That said, i also need this formula to now look at the year as a criteria as well. As of now it is returning both cities in a region although the data is for different years. I am attaching the correct sheet herein.

[JeteMc]

Try the following:
For G5 and down (based on Ace_XL's formula):

Please Login or Register  to view this content.

For H5 and down: =IF(G5="","",SUMIFS(Table1[Budget],Table1[Year],B$3,Table1[Region],D$3,Table1[City],G5))
Let us know if you have any questions.