How To Calculate Conditional Mode On Text?

[xlmaniac]

Dear All,
I do have a sample data set as follows(A1:B5)
I would like to calculate the Conditional Mode against the Text in D1.C1 Contains Code(A).
Pls suggest the formula which can populate the result in D1?
[TABLE="width: 128"]
<tbody>[TR]
[TD]Code
[/TD]
[TD]Day
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Thursday
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Thursday
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Monday
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]Thursday
[/TD]
[/TR]
</tbody>[/TABLE]

Regards

 

[xlmaniac]

Desired result will be Thursday against code A

 

[FormR]

Hi, you can try this..:

Excel 2013/2016
	A	B	C	D
1	Code	Day	A	Thursday
2	A	Thursday
3	A	Thursday
4	A	Monday
5	B	Thursday
flight track

Cell Formulas
Range	Formula
D1	{=INDEX(B2:B5,MODE(IF(A2:A5=C1,MATCH(B2:B5,B2:B5,0))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[xlmaniac]

Hi, you can try this..:

Excel 2013/2016

	A	B	C	D
A

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Code[/TD]
[TD="bgcolor: #FFFFFF"]Day[/TD]

[TD="bgcolor: #FFFF00"]Thursday[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]A[/TD]
[TD="bgcolor: #FFFFFF"]Thursday[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]A[/TD]
[TD="bgcolor: #FFFFFF"]Thursday[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]A[/TD]
[TD="bgcolor: #FFFFFF"]Monday[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]B[/TD]
[TD="bgcolor: #FFFFFF"]Thursday[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

flight track​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]{=INDEX(B2:B5,MODE(IF(A2:A5=C1,MATCH(B2:B5,B2:B5,0))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Dear FORMR,
Thanks a lot for the wonderful solution.
You have made my day.
Thanks once again for all the effort.
Really appreciate the same.
Regards