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
1	Company	Service Name	Category		Criteria		Output
2	Parliamentary Service	WAN Service Lite@SILVER	Network		Company		Services
3	Parliamentary Service	Voice Connect@SILVER	Voice		Parliamentary Service		WAN Service Lite@SILVER
4	Parliamentary Service	WAN Service Premium@GOLD	Network		Category		WAN Service Premium@GOLD
5	Parliamentary Service	WAN Service Delivery Platform@SILVER	Network		Network		WAN Service Delivery Platform@SILVER
6	Parliamentary Service	WAN Service Premium@SILVER	Mobile				WAN Service Delivery Platform@SILVER
7	Parliamentary Service	WAN Service Delivery Platform@SILVER	Network
8	Parliamentary Service	Voice Connect@SILVER	Voice
9	Ministry of Justice	TaaS GNet Site Connectivity@MOJNZXA	Network
10	Ministry of Justice	Corporate Mobile Direct@GOLD	Mobile
11	Ministry of Justice	eFax@SILVER	Voice
12	Ministry of Justice	Frame Relay@GOLD	IT
13	Ministry of Justice	Gen-i Direct Connect@GOLD	Mobile
14	Ministry of Justice	Internet Service Plus@GOLD	Voice
15	Ministry of Justice	WAN Service Premium@GOLD	Network
16	Ministry of Justice	WAN Service Foundation@GOLDY	Network
17
18
19
20
21
22
23	Ministry of Justice	Voice
24	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>
</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