Help wth Vlookup problem?

[k11ngy]

I have attached spreadsheet book ifi helps and have tried several attemts but no success

The data comes with a week number, and type. On the front page, I have 2 cells made for week number and type, I need it so that when I input the week number and type in the yellow cells, ALL the references that match the 2 criteria will be listed in the ref column.



So if I say week number 1, Type A, I need all the references that are week 1 and type A to be listed. Within same week number, you can have multiple reference for the same type (so you can have like 3 type A within same week).

Appreciate an help
Thank you

Steve

[DBY]

Hello Steve
There a a few ways to do this. If as you say there might be duplicate values for Week and Type, one way would be to use Array formulas (although you could use non-array formulas, or helper columns). But perhaps the easiest way would be to use a Pivot table. See attached.

Hope this helps.
DBY

[Jean.P28]

With unique week and type formula for the validation

[k11ngy]

Thank you so much for help

Steve

[k11ngy]

Excel Forum.xlsm

Marked as solved on forum but I still seem to have problem. On template sheet I typed week 26 and selected PSB but it came up Fennel underneath, not sure if error from me and was wondering if easy to amend formula to fix problem?

Thanks again and appreciate help

Steve

File attached called EXCEL FORUN

[Ace_XL]

Shouldn't C22 be

=IFERROR(INDEX(Database!D$4:D$2000,SMALL(IF((Database!$A$4:$A$2000=Template!$C$16)*(Database!$E$4:$E$2000=Template!$C$18),ROW(Database!$A$1:$A$1996)),ROWS($C$22:C22))),"")

[Fotis1991]

Modify the ARRAY formula in C22 like this.

=IFERROR(INDEX(Database!D$4:D$2000,SMALL(IF((Database!$A$4:$A$2000=Template!$C$16)*(Database!$E$4:$E$2000=Template!$C$18),ROW(Database!$D$4:$D$2000)-3),ROW(Database!D1))),"")

Copy down.