If, countif, index

J

joetogo

New Member
Joined
Apr 16, 2017
Messages
12
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?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Re: If, countif, index help!! :(

Try this:
Drag formula down as needed. This is an array formula that must be entered with CTRL-SHIFT-ENTER.

You were not picking up the first item because your row formula ROW($A$2:$A$16) was returning
{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16} and not starting with 1.

Excel Workbook
ABCDEFG
1CompanyService NameCategoryCriteriaOutput
2Parliamentary ServiceWAN Service Lite@SILVERNetworkCompanyServices
3Parliamentary ServiceVoice Connect@SILVERVoiceParliamentary ServiceWAN Service Lite@SILVER
4Parliamentary ServiceWAN Service Premium@GOLDNetworkCategoryVoice Connect@SILVER
5Parliamentary ServiceWAN Service Delivery Platform@SILVERNetworkVoice
6Parliamentary ServiceWAN Service Premium@SILVERMobile
7Parliamentary ServiceWAN Service Delivery Platform@SILVERNetwork
8Parliamentary ServiceVoice Connect@SILVERVoice
9Ministry of JusticeTaaS GNet Site Connectivity@MOJNZXANetwork
10Ministry of JusticeCorporate Mobile Direct@GOLDMobile
11Ministry of JusticeeFax@SILVERVoice
12Ministry of JusticeFrame Relay@GOLDIT
13Ministry of JusticeGen-i Direct Connect@GOLDMobile
14Ministry of JusticeInternet Service Plus@GOLDVoice
15Ministry of JusticeWAN Service Premium@GOLDNetwork
16Ministry of JusticeWAN Service Foundation@GOLDYNetwork
17
18
19
20
21
22
23Ministry of JusticeVoice
24Parliamentary ServiceNetwork
25IT
26Mobile
Sheet
 
Upvote 0
Re: If, countif, index help!! :(

Care to post precisely list what the desired results are when Company = Ministry of Justice and Category = Network?
 
Upvote 0
Re: If, countif, index help!! :(

Aladin Akyurek said:
Care to post precisely list what the desired results are when Company = Ministry of Justice and Category = Network?
Click to expand...

Hi Aladin,

I'd like a list of the "Services" when the Company = MOJ and Category = Network. So the list will be:

TaaS GNet Site Connectivity@MOJNZXA
WAN Service Premium@GOLD
WAN Service Foundation@GOLDY

Company and Category will be dynamic affecting the list content and length as they change. Hope that clarifies?
 
Upvote 0
Re: If, countif, index help!! :(

AhoyNC said:
Try this:
Drag formula down as needed. This is an array formula that must be entered with CTRL-SHIFT-ENTER.

You were not picking up the first item because your row formula ROW($A$2:$A$16) was returning
{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16} and not starting with 1.

ABCDEFG
CompanyService NameCategory Criteria Output
Company Services
WAN Service Lite@SILVER
Category Voice Connect@SILVER
Ministry of JusticeVoice
Parliamentary ServiceNetwork
IT
Mobile

<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: [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: [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"]Voice[/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: [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]

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

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

</tbody>

Spreadsheet Formulas
CellFormula
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,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
Click to expand...

Hi AhoyNC,

I've tried your formula above, but now I'm coming up with a blank result? I've gone through the formula
evaluation and comes up blank also? Is entered as an array formula.

Thank you for your help!
 
Upvote 0
Re: If, countif, index help!! :(

I downloaded your file as you can see it works for me. Not sure why you are getting blanks.
Excel Workbook
ABCDEFG
1CompanyService NameCategoryCriteriaOutput
2Parliamentary ServiceWAN Service Lite@SILVERNetworkCompanyServices
3Parliamentary ServiceVoice Connect@SILVERVoiceMinistry of JusticeTaaS GNet Site Connectivity@MOJNZXA
4Parliamentary ServiceWAN Service Premium@GOLDNetworkCategoryCorporate Mobile Direct@GOLD
5Parliamentary ServiceWAN Service Delivery Platform@SILVERNetworkNetworkeFax@SILVER
6Parliamentary ServiceWAN Service Premium@SILVERMobile
7Parliamentary ServiceWAN Service Delivery Platform@SILVERNetwork
8Parliamentary ServiceVoice Connect@SILVERVoice
9Ministry of JusticeTaaS GNet Site Connectivity@MOJNZXANetwork
10Ministry of JusticeCorporate Mobile Direct@GOLDMobile
11Ministry of JusticeeFax@SILVERVoice
12Ministry of JusticeFrame Relay@GOLDIT
13Ministry of JusticeGen-i Direct Connect@GOLDMobile
14Ministry of JusticeInternet Service Plus@GOLDVoice
15Ministry of JusticeWAN Service Premium@GOLDNetwork
16Ministry of JusticeWAN Service Foundation@GOLDYNetwork
17
18
19
20
21
22
23Ministry of JusticeVoice
24Parliamentary ServiceNetwork
Sheet
 
Upvote 0
Re: If, countif, index help!! :(

AhoyNC said:
I downloaded your file as you can see it works for me. Not sure why you are getting blanks.

ABCDEFG
CompanyService NameCategory Criteria Output
Company Services
Category
Ministry of JusticeVoice
Parliamentary ServiceNetwork

<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"]Ministry of Justice[/TD]

[TD="bgcolor: #ffff00"]TaaS GNet Site Connectivity@MOJNZXA[/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"]Corporate Mobile Direct@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"]eFax@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: [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
CellFormula
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,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
Click to expand...

Hmmm, I'll double check, I'm doing something wrong :)

Thank you once again! I'll message when i successfully get it to work :D
 
Upvote 0
Re: If, countif, index help!! :(

AhoyNC said:
I downloaded your file as you can see it works for me. Not sure why you are getting blanks.

ABCDEFG
CompanyService NameCategory Criteria Output
Company Services
Category
Ministry of JusticeVoice
Parliamentary ServiceNetwork

<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"]Ministry of Justice[/TD]

[TD="bgcolor: #ffff00"]TaaS GNet Site Connectivity@MOJNZXA[/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"]Corporate Mobile Direct@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"]eFax@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: [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
CellFormula
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,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
Click to expand...


Hi AhoyNZ,

One thing though is that the categories don't match when you change the criteria in the dynamic cells.
For e.g.

Company = Parliamentary Service & Category = Network should result in:

WAN Service Lite@SILVER
WAN Service Premium@GOLD
WAN Service Delivery Platform@SILVER
WAN Service Delivery Platform@SILVER (is a dupe, unsure why that is there as it shouldn't)

Does that make sense?

Thank you once again for your assistance.
 
Upvote 0
Re: If, countif, index help!! :(

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

Should be:
Code: 
[TABLE="width: 1531"]
<colgroup><col width="1531"></colgroup>[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]
[/TABLE]
Excel Workbook
ABCDEFG
1CompanyService NameCategoryCriteriaOutput
2Parliamentary ServiceWAN Service Lite@SILVERNetworkCompanyServices
3Parliamentary ServiceVoice Connect@SILVERVoiceParliamentary ServiceWAN Service Lite@SILVER
4Parliamentary ServiceWAN Service Premium@GOLDNetworkCategoryWAN Service Premium@GOLD
5Parliamentary ServiceWAN Service Delivery Platform@SILVERNetworkNetworkWAN Service Delivery Platform@SILVER
6Parliamentary ServiceWAN Service Premium@SILVERMobileWAN Service Delivery Platform@SILVER
7Parliamentary ServiceWAN Service Delivery Platform@SILVERNetwork
8Parliamentary ServiceVoice Connect@SILVERVoice
9Ministry of JusticeTaaS GNet Site Connectivity@MOJNZXANetwork
10Ministry of JusticeCorporate Mobile Direct@GOLDMobile
11Ministry of JusticeeFax@SILVERVoice
12Ministry of JusticeFrame Relay@GOLDIT
13Ministry of JusticeGen-i Direct Connect@GOLDMobile
14Ministry of JusticeInternet Service Plus@GOLDVoice
15Ministry of JusticeWAN Service Premium@GOLDNetwork
16Ministry of JusticeWAN Service Foundation@GOLDYNetwork
17
18
19
20
21
22
23Ministry of JusticeVoice
24Parliamentary ServiceNetwork
Sheet
 
Upvote 0
Re: If, countif, index help!! :(

joetogo said:
Hi Aladin,

I'd like a list of the "Services" when the Company = MOJ and Category = Network. So the list will be:

TaaS GNet Site Connectivity@MOJNZXA
WAN Service Premium@GOLD
WAN Service Foundation@GOLDY

Company and Category will be dynamic affecting the list content and length as they change. Hope that clarifies?
Click to expand...

What is your Excel version?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Excel INDEX MATCH with 3 criteria not working
Replies
3
Views
290
etaf
etaf
R
  • Question Question
Can you help with this code please?
Replies
3
Views
370
rlv01
R
C
  • Solved
Nested IF formula with multiple criteria not working
2
Replies
17
Views
834
CaptainGravyBum
C
M
  • Solved
Countifs not working due to range ? maybe an array?
2
Replies
14
Views
3K
etaf
etaf
S
  • Question Question
Sum Subtotals Based on numbered subsections
2
Replies
11
Views
593
sameri
S

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top