A vlookup that can return all possible values

[DashboardNovice]

On the 'Reference' tab in column G, I am trying to come up with a formula in the yellow cells that would return the values in the yellow cells.

Starting in G2, I want to use some lookup function that uses G1 as a lookup value, then points to column B in the 'Consolidated' tab, and returns all possible values in column C.

In this example, on the 'Reference' tab, 8 DEC 23 (23) 100 (Weeklys) would be used as the look up value, which would be found on the 'Consolidated' tab in cells B75:B95, then return the values in C75:C95 to the 'Reference' tab in cells G2:G22.

Is there a look up function that can do this? VLOOKUP will stop only on the 1st occurrence.

 

[Monty]

Hello

I think you can use the combination of INDEX and SMALL functions along with IF and ROW functions to create an array formula. Place the following formula in cell G2 of your 'Reference' tab:

=IFERROR(INDEX('Consolidated'!C$75:C$95, SMALL(IF('Consolidated'!B$75:B$95=G$1, ROW('Consolidated'!B$75:B$95)-ROW('Consolidated'!B$75)+1), ROW(1:1))), "")

 

[pecoflyer]

Applying

=FILTER(Consolidated!$C$2:$C$1509;Consolidated!$B$2:$B$1509=Reference!$G$1;"")

returns more values than your example ( 40 in total)
Applying a filter to your "expiration" column also returns 40 values, not 21. Are there any other constraints?

 

[p45cal]

Are there any other constraints?

looks like just the calls, in G2:

=FILTER(Consolidated!$C2:$C1509,(G$1=Consolidated!$B2:$B1509)*(Consolidated!$E2:$E1509="CALL"))

this will copy to alternate cells left and right.

 

[DashboardNovice]

This filter functions is awesome. I didn't know about it. I was able to get what I need. Thank you everybody for your help.