Match and index with additional column

[ashwithonline]

Hi Friends,

In the attached example, I need the pass / fail / blocked count for particular domain and date,

ex: Failed count for Domain C and 07/12/2025.

Thanks in advance

 

[ETAF]

OH OK< Now AliGW has posted - i see what you wanted

for Older versions of excel
=INDEX(D$4:D$15,MATCH($H$3&$I$3,$B$4:$B$15&$C$4:$C$15,0))


sorry not following the logic - why is your ex ...
what version of excel are you using

can you give some expected results and why - based on the example table

 

[AliGW]

I'll assume you have 365.

In H3: C
In I3: 07/12/2025
In J3 followed by ENTER:
=DROP(GROUPBY(HSTACK(B4:B15,C4:C15),HSTACK(D4:D15,E4:E15,F4:F15),SUM,,0,,(B4:B15=H3)*(C4:C15=I3)),,2)

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	H​	I​	J​	K​	L​
2​	Domain​	Date​	Pass​	Fail​	Blocked​
3​	C​	12/07/2025​	2​	4​	2​

Sheet: Sheet1

 

[ashwithonline]

OH OK< Now AliGW has posted - i see what you wanted

for Older versions of excel
=INDEX(D$4:D$15,MATCH($H$3&$I$3,$B$4:$B$15&$C$4:$C$15,0))


sorry not following the logic - why is your ex ...
what version of excel are you using

can you give some expected results and why - based on the example table

The results that you have shown in the excel are correct, I want in the same fashion...but you have selected that particular column to bring the result.
Can we have something else such that the formula becomes dynamic and no matter where the column is placed it should pick it like we get in match and index

 

[ashwithonline]

I'll assume you have 365.

In H3: C
In I3: 07/12/2025
In J3 followed by ENTER:
=DROP(GROUPBY(HSTACK(B4:B15,C4:C15),HSTACK(D4:D15,E4:E15,F4:F15),SUM,,0,,(B4:B15=H3)*(C4:C15=I3)),,2)

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	H​	I​	J​	K​	L​
2​	Domain​	Date​	Pass​	Fail​	Blocked​
3​	C​	12/07/2025​	2​	4​	2​

Sheet: Sheet1

this worked..but is a bit complex for my brain...

 

[ashwithonline]

this worked..but is a bit complex for my brain...

also I guess the formula will not work in case the arrangement of status is changed... ex: Fail, Blocked and pass

 

[ETAF]

maybe this
=INDEX($D$4:$F$15,MATCH($H$3&$I$3,$B$4:$B$15&$C$4:$C$15,0),MATCH(J2,$D$3:$F$3,0))

 

[AliGW]

also I guess the formula will not work in case the arrangement of status is changed... ex: Fail, Blocked and pass

Threse are things you need to mention at the outset.

 

[ashwithonline]

it worked...thanks everybody for the help

 

[ETAF]

you are welcome

 

[AliGW]

Please feel free to use the LIKE button under our posts.