If, countif, index

[joetogo]

Hi team,

I feel like I'm not that far off from what i want to achieve, but what I'm essentially trying to do is
have multiple criteria reference a table where there are 2 criteria are dynamic ref cells.

With Sheet2, i managed to get it to work somewhat, but it the result doesn't show the first Service
for the customer in the table. As in it lists all the correct services except for the first. I'm not sure how
to get the second criteria "Category" to work either.

Sheet1's formula does what I want it to, but instead of displaying all the relevant records, it dupes the
same record.

https://www.dropbox.com/s/6sw2aaxdk0hu1v0/TestSheet.xlsx?dl=0

Can anybody please help?

 

[joetogo]

Re: If, countif, index help!!

my excel version is 1710 (Build 8625.2127) - part of my O365PP package

 

[joetogo]

Re: If, countif, index help!!

You sir are the man. This is now working perfectly. Thank you so much for your assistance!!!

 

[joetogo]

Re: If, countif, index help!!

I see the problem. I left out an IF statement in the second part of the formula.

Should be:

[TABLE="width: 1531"]
<tbody>[TR]
[TD="width: 1531"]=IF(ISERROR(INDEX($A$2:$C$16,SMALL(IF($A$2:$A$16=$E$3,IF($C$2:$C$16=$E$5,ROW($A$2:$A$16)-ROW($A$2)+1)),ROWS($G$3:G3)),2)),"",INDEX($A$2:$C$16,SMALL(IF($A$2:$A$16=$E$3,[COLOR=#ff0000]IF($C$2:$C$16=$E$5[/COLOR],ROW($A$2:$A$16)-ROW($A$2)+1)),ROWS($G$3:G3)),2))[/TD]
[/TR]
</tbody>[/TABLE]

	A	B	C	D	E	F	G
Company	Service Name	Category		Criteria		Output
	Company		Services
	Category
Ministry of Justice	Voice
Parliamentary Service	Network

<colgroup><col style="width:30px; "><col style="width:130px;"><col style="width:227px;"><col style="width:78px;"><col style="width:64px;"><col style="width:143px;"><col style="width:64px;"><col style="width:256px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: #ffffcc"]Parliamentary Service[/TD]
[TD="bgcolor: #ffffcc"]WAN Service Lite@SILVER[/TD]
[TD="bgcolor: #ffffcc"]Network[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: #ffffcc"]Parliamentary Service[/TD]
[TD="bgcolor: #ffffcc"]Voice Connect@SILVER[/TD]
[TD="bgcolor: #ffffcc"]Voice[/TD]

[TD="bgcolor: #ccffcc"]Parliamentary Service[/TD]

[TD="bgcolor: #ffff00"]WAN Service Lite@SILVER[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: #ffffcc"]Parliamentary Service[/TD]
[TD="bgcolor: #ffffcc"]WAN Service Premium@GOLD[/TD]
[TD="bgcolor: #ffffcc"]Network[/TD]

[TD="bgcolor: #ffff00"]WAN Service Premium@GOLD[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="bgcolor: #ffffcc"]Parliamentary Service[/TD]
[TD="bgcolor: #ffffcc"]WAN Service Delivery Platform@SILVER[/TD]
[TD="bgcolor: #ffffcc"]Network[/TD]

[TD="bgcolor: #ccffcc"]Network[/TD]

[TD="bgcolor: #ffff00"]WAN Service Delivery Platform@SILVER[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="bgcolor: #ffffcc"]Parliamentary Service[/TD]
[TD="bgcolor: #ffffcc"]WAN Service Premium@SILVER[/TD]
[TD="bgcolor: #ffffcc"]Mobile[/TD]

[TD="bgcolor: #ffff00"]WAN Service Delivery Platform@SILVER[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="bgcolor: #ffffcc"]Parliamentary Service[/TD]
[TD="bgcolor: #ffffcc"]WAN Service Delivery Platform@SILVER[/TD]
[TD="bgcolor: #ffffcc"]Network[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="bgcolor: #ffffcc"]Parliamentary Service[/TD]
[TD="bgcolor: #ffffcc"]Voice Connect@SILVER[/TD]
[TD="bgcolor: #ffffcc"]Voice[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="bgcolor: #ccccff"]Ministry of Justice[/TD]
[TD="bgcolor: #ccccff"]TaaS GNet Site Connectivity@MOJNZXA[/TD]
[TD="bgcolor: #ccccff"]Network[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="bgcolor: #ccccff"]Ministry of Justice[/TD]
[TD="bgcolor: #ccccff"]Corporate Mobile Direct@GOLD[/TD]
[TD="bgcolor: #ccccff"]Mobile[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="bgcolor: #ccccff"]Ministry of Justice[/TD]
[TD="bgcolor: #ccccff"]eFax@SILVER[/TD]
[TD="bgcolor: #ccccff"]Voice[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="bgcolor: #ccccff"]Ministry of Justice[/TD]
[TD="bgcolor: #ccccff"]Frame Relay@GOLD[/TD]
[TD="bgcolor: #ccccff"]IT[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="bgcolor: #ccccff"]Ministry of Justice[/TD]
[TD="bgcolor: #ccccff"]Gen-i Direct Connect@GOLD[/TD]
[TD="bgcolor: #ccccff"]Mobile[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="bgcolor: #ccccff"]Ministry of Justice[/TD]
[TD="bgcolor: #ccccff"]Internet Service Plus@GOLD[/TD]
[TD="bgcolor: #ccccff"]Voice[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="bgcolor: #ccccff"]Ministry of Justice[/TD]
[TD="bgcolor: #ccccff"]WAN Service Premium@GOLD[/TD]
[TD="bgcolor: #ccccff"]Network[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="bgcolor: #ccccff"]Ministry of Justice[/TD]
[TD="bgcolor: #ccccff"]WAN Service Foundation@GOLDY[/TD]
[TD="bgcolor: #ccccff"]Network[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]23[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]24[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G3 {=IF(ISERROR(INDEX($A$2:$C$16,SMALL(IF($A$2:$A$16=$E$3,IF($C$2:$C$16=$E$5,ROW($A$2:$A$16)-ROW($A$2)+1)),ROWS($G$3:G3)),2)),"",INDEX($A$2:$C$16,SMALL(IF($A$2:$A$16=$E$3,IF($C$2:$C$16=$E$5,ROW($A$2:$A$16)-ROW($A$2)+1)),ROWS($G$3:G3)),2))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you so much AhoyNC - working perfectly!!!

 

[Aladin Akyurek]

Re: If, countif, index help!!

my excel version is 1710 (Build 8625.2127) - part of my O365PP package

Are you on Excel 2007, on Excel 2013, or on Excel 2016?

 

[joetogo]

Re: If, countif, index help!!

Hi Aladin, I'm on Excel 2016