Creating a List With 2 Criteria and Allowing Duplicate Return Values

[Ekazumon]

GREETINGS!


Context:
I’m attempting to create a horizontal list that dynamically shifts. The data comes from a standard table with 4 columns. My main issue is that I am unable to figure out a formula that creates a list that has 2 criteria and allows for duplicate values.

Criteria 1: Items match the searched item’s manf. #
Criteria 2: Item has been not been used.

Goal: List of all applicable expiration dates. (Some unsued items may have the same expiration date).


I am familiar with using iserror, index, small, row to create a list that allows for duplicate values but with only on condition.

I am also familiar with using index, match, countif to get a unique list with multiple conditions.

For some reason, I can’t figure out how to add one more criteria to a duplicate allowing list. Any help would be greatly appreciated.

[bebo021999]

Try in G2:

Please Login or Register  to view this content.

Because you are getting familiar with INDEX(MATCH,SMALL,... then I 'd like to introduce AGGREGATE which does not require Ctrl-Shift_enter

[Bo_Ry]

Please select G4:U4

=IFERROR(AGGREGATE(15,6,Table3[Expiration]/(Table3[Mfg Itm ID]=TEXT($F4,"0000000"))/NOT(Table3[USED]),COLUMNS($G4:G4)),"")
Confirm by Ctrl+Enter

or MS365
=TRANSPOSE(SORT(FILTER(Table3[Expiration],(Table3[Mfg Itm ID]=TEXT($F4,"0000000"))*NOT(Table3[USED]))))

[Neptuns]

Thank you. I have been lookig for this solution for some time.




Best Regards