Index, Small, Row issue with Multiple Values and need Multiple Results

[jmantn]

Hello!

I'm trying to create a spreadsheet dealing with multiple schedules for multiple people in two buildings and I'm running into some issues.

I'm using Excel 2010 and I have two spreadsheets within a single workbook, my first sheet is called "RawData" and my second sheet is called "Filtered".

On my Filtered sheet I have two drop downs lists in cells A9 & B9 that select a day of the week and a building they work in which work as expected. I need to have these two criteria as people report to different buildings on different days.

I've been trying to figure out how to populate data on my Filtered sheet from my RawData sheet via my two drop downs and want to avoid using Vlookup for extensibility reasons (may want to change what the criteria is at some point or need to add a column).

I am currently using named ranges and have tried the following formula's to no avail and I've been using Control + Shift + Enter to complete these:

Please Login or Register  to view this content.

This populates the correct name but duplicates it when dragged down, I can't figure out a way to make this increment by one so switched to using Small instead.

Please Login or Register  to view this content.

This populates names that don't work on Saturday in building A for example, feel like this is close but it's off somehow.

Please Login or Register  to view this content.

This populates the wrong name and then duplicates it. Way off with this approach.


To paint a picture for those not wanting to download an attachment:
Example of RawData Sheet starting on Row A5:

Day(A5)_______Bldg(B5)_____Agent(C5)
Sunday________A__________Jane Doe
Monday________B__________Jane Doe
Tuesday_______A__________Jane Doe
Wednesday_____A__________Jane Doe
Thursday_______B__________Jane Doe
Monday________B__________Todd Doe
Tuesday________A__________Todd Doe
Wednesday_____A__________Todd Doe
Thursday_______B__________Todd Doe
Friday__________A__________Todd Doe
Saturday_______A__________Britt Doe
Monday_______A__________Britt Doe
Tuesday_______A__________Britt Doe
Wednesday____A__________Britt Doe

Named Ranges for example workbook are: Day for A6 to A56, Bldg for B6 to B56, and Agent for C6 to C56 which all pertain to my RawData Sheet.

On my filtered sheet I select a day such as Saturday from a list in A9 and Select a building such as A from a list in B9 and I want to populate all agents working that meet those two criteria in D9 to D60 for example. If possible I'd also like to know how to add a 3rd or 4th criteria but this can definitely wait.

I'll also attach a sample sheet of what I'm dealing with which also shows the errors I'm receiving.

Thanks in advance and apologies for the impromptu chart, didn't see any chart formatting options

[DGagnon]

Try this array formula in E9 and fill down

=INDEX(RawData!C$1:C$85,SMALL(IF($B$9=RawData!B$6:B$85,IF(RawData!A$6:A$85=$A$9,ROW(RawData!C$6:C$85),9^99),9^99),ROW()-8))

you could always add an iferror to it to clean up the results as well.

[Haseeb Avarakkan]

Hello,

In E9 with CTRL+SHIFT+ENTER, then copy across & down.

Please Login or Register  to view this content.

[jmantn]

Many Thanks to both DGanon & Haseeb A!!!!

DGanon your solution didn't have the desired solution as it was pulling in Names that were not correct for the selected criteria however that was an interesting approach with your formula I had not yet tried.

Haseeb A, your solution worked perfectly!!! I'm going to have to devote some time understanding how that works for when I finally add a third and fourth criteria assuming it's possible. Thanks doesn't cut it, I've spent 3 days working on this, just know that was very much appreciated.

By the way would it be possible to add a third or fourth criteria or would that require a different approach? I figured I'd ask as my mind is numb from dealing with this at the moment.

[DGagnon]

Yes, basicaly what both Hasseb and myself did was to create a nested if array, inside of a small formula. the difference between the 2 formula was that Haseeb included all columns, where as mine only looked to the Names, and Hasseb also used a better method to determin the second paramater in the Small function. my formula relied on knowing the current row and subtracting to find the indexed row, where as Hasseb used the rows function to determin how many rows down you were in that sequence.

there was a typo in my formula that i now see, i was looking at the range of RawData!C$1:C$85 instead of RawData!C$6:C$85 for a name, but using Hasseb's formula you can expand the criteria by expanding the nested if,

IF(Day=$A$9,IF(Bldg=$B$9,IF(NEWTHING=NEW,

just remembering to close out the if after the ROW(INDEX(Day,1,1))+1 portion.

formula again included bellow for reference.

=IFERROR(INDEX(RawData,SMALL(IF(Day=$A$9,IF(Bldg=$B$9,ROW(Day)-ROW(INDEX(Day,1,1))+1)),ROWS(E$9:E9)),COLUMNS($E9:E9)+2),"")

[jmantn]

Thanks so much DGanon that makes perfect sense.

[cowboy1978sg]

Dear all,

This is awesome, thank you! I've looked all over for something like this!

I have a worksheet that is very similar but with 3 sets of criteria, but somehow it doesn't seem to work.

Here is the formula:
=IFERROR(INDEX(Data,SMALL(IF(Quarter=$A$4,IF(AccType=$A$5,IF(IncExp=$A$13,ROW(Quarter)-ROW(INDEX(Quarter,1,1))+1))),ROWS(A$14:A14)),COLUMNS($A14:A14)+3),"-")

So I basically have Quarter, Account Type (AccType) and Income/Expenditure (IncExp) in B, C, and D columns respectively and I'm trying to return an item from column E.

Currently, all I am getting are "-" outputs, without anything returning from column E.

Am I missing something in this formula? Please assist if you can, thanks.

[protonLeah]

cowboy1978sg,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.