Getting results with multiple criteria and multiple results

[legoboy177]

Hello all,

I have a worksheet that has all my board games and relevant playing data.

Column A is the minimum players
Column B is the Maximum players
Column C is the Game Type (Board, Dice, Etc.)
Column D is the Play Type (Co-Op or Comp)
Column E is the Game’s Title

I was to have a section where I can type in the number of players, the game type, and the play type and it give me all possible games that fit that criteria.

The number of players I input would have to fall in between and including the minimum and maximum players allowed for that game.

I have looked up multiple different formulas for an Array with Small, If, and Index but none of them seem to work correctly.

I do NOT want to use VBA.

Any help would be greatly appreciated.

 

[RasGhul]

Can you post some sample data?

 

[DanteAmor]

Look at the following example, in cells H5, H6 and H7 you put the criteria. The formula is array to accept you must press Shift + Control + Enter.

It is important that the formula is in all the rows where you have data, in my example the data goes from row 2 to 7, in the same way the formulas must be from J2 to J7.
You can copy the formula in cells J2 to J7, select the cells from J2 to J7, press the F2 key to edit, then to accept press the Shift + Control + Enter keys


<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:88px;" /><col style="width:91px;" /><col style="width:76px;" /><col style="width:69px;" /><col style="width:76px;" /><col style="width:26px;" /><col style="width:91px;" /><col style="width:91px;" /><col style="width:27px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; text-align:center; ">MIN PLAYERS</td><td style="background-color:#92d050; text-align:center; ">MAX PLAYERS</td><td style="background-color:#92d050; text-align:center; ">GAME TYPE</td><td style="background-color:#92d050; text-align:center; ">PLAY TYPE</td><td style="background-color:#92d050; text-align:center; ">TITLE</td><td > </td><td style="background-color:#92d050; ">NUM PLAYERS</td><td style="background-color:#ffff00; text-align:center; ">5</td><td > </td><td style="background-color:#92d050; text-align:center; ">TITLE</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; ">2</td><td style="text-align:center; ">8</td><td >Board</td><td >Co-op</td><td >Monopoly</td><td > </td><td style="background-color:#92d050; ">GAME TYPE</td><td style="background-color:#ffff00; text-align:center; ">Board</td><td > </td><td >Risk</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">2</td><td style="text-align:center; ">4</td><td >Board</td><td >Co-op</td><td >Scrabble</td><td > </td><td style="background-color:#92d050; ">PLAY TYPE</td><td style="background-color:#ffff00; text-align:center; ">Comp</td><td > </td><td >One</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; ">4</td><td style="text-align:center; ">6</td><td >Board</td><td >Comp</td><td >Risk</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; ">3</td><td style="text-align:center; ">5</td><td >Dice</td><td >Comp</td><td >Beat</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; ">2</td><td style="text-align:center; ">4</td><td >Dice</td><td >Comp</td><td >RUN</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:center; ">5</td><td style="text-align:center; ">7</td><td >Board</td><td >Comp</td><td >One</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >J2</td><td >{=IFERROR(INDEX($E$1:$E$7, SMALL(IF($A$2:$A$7<=$H$1, IF($B$2:$B$7>=$H$1,IF($C$2:$C$7=$H$2,IF($D$2:$D$7=$H$3,ROW())))), ROW()-1)),"")}</td></tr></table></td></tr></table> <br /><br />

 

[legoboy177]

Look at the following example, in cells H5, H6 and H7 you put the criteria. The formula is array to accept you must press Shift + Control + Enter.

It is important that the formula is in all the rows where you have data, in my example the data goes from row 2 to 7, in the same way the formulas must be from J2 to J7.
You can copy the formula in cells J2 to J7, select the cells from J2 to J7, press the F2 key to edit, then to accept press the Shift + Control + Enter keys


Sheet

	A	B	C	D	E	F	G	H	I	J
1	MIN PLAYERS	MAX PLAYERS	GAME TYPE	PLAY TYPE	TITLE		NUM PLAYERS	5		TITLE
2	2	8	Board	Co-op	Monopoly		GAME TYPE	Board		Risk
3	2	4	Board	Co-op	Scrabble		PLAY TYPE	Comp		One
4	4	6	Board	Comp	Risk
5	3	5	Dice	Comp	Beat
6	2	4	Dice	Comp	RUN
7	5	7	Board	Comp	One

<tbody>
</tbody>

Formulas

Cell Array Formula J2 {=IFERROR(INDEX($E$1:$E$7, SMALL(IF($A$2:$A$7<=$H$1, IF($B$2:$B$7>=$H$1,IF($C$2:$C$7=$H$2,IF($D$2:$D$7=$H$3,ROW())))), ROW()-1)),"")} <tbody> </tbody>

<tbody>
</tbody>

Thanks though i'm still getting inaccurate result. I've attached a link to my document.

https://1drv.ms/x/s!Amu3QUCCAymHvhKvgqBmvXaJCUGm

 

[DanteAmor]

It is important that the formula is in all the rows where you have data,

The formula in your file only goes to row 24, you must copy it to row 45.
Select from cell J2 to J45 then press F2 to edit the formula and now press Shift + Control + Enter


As shown in the following file

https://www.dropbox.com/s/tjffigsmnu1llo1/Game Selection dam.xlsx?dl=0