Index Match function to find outliers

[Hiten_pan]

Hi friends,


I am working on a project where I would need below raw data to show expected results as shown below.
Basically, it is an outlier report where only "Check Accts" will be flagged. Thanks!!!
I am looking for the formulae without {} brackets, it maybe combination of Index Match function

Raw data

Unique #	Acct	Cusip	Direction	Broker Accruals	PIMCO Accruals
695CCHUSIUS4	695	CCHUSIUS4	IP	1738.51	1733.52
1023CCHUSIUS4	1023	CCHUSIUS4	IP	1109.51	1076.08
3199CCHUSIUS4	3199	CCHUSIUS4	IP	1099.89	CHECK Acct#
1203CCHUSIUS4	1203	CCHUSIUS4	IP	772.23	773.85
2755CCHUSIUS4	2755	CCHUSIUS4	IP	417.71	417.66
96CCHUSIUS4	96	CCHUSIUS4	IP	194.31	194.57
2462CCHUSIUS4	2462	CCHUSIUS4	IP	181.3	181.55
3684CCHUSIUS4	3684	CCHUSIUS4	IP	119.94	120
7893CCHUSIUS4	7893	CCHUSIUS4	IP	85.5	CHECK Acct#
935CCHUSIUS4	935	CCHUSIUS4	IP	80.13	80.13

<tbody>
</tbody>

Expected results

PIMCO Accruals	Unique #
CHECK Acct#	3199CCHUSIUS4
CHECK Acct#	7893CCHUSIUS4

<tbody>
</tbody>

 

[KevCarter]

Also nice. I do think people get a little crazy trying to avoid ctrl-shift-enter though!

Agree. However, in my real world projects, I go with something like your helper column example whenever possible. In my opinion, much easier to look at later and remember what's happening if I need to update or change. It's still fun to learn different ideas like Mark's cool formula above.

Cheers!

 

[markmzz]

Nice!

Thank you.

Markmzz

 

[Hiten_pan]

Sheetspread & KavCarter, you guys are genius, the formula is just mind blowing. too good. I was using {} formulae which was very complex and file gets hanged. Thank god guys, the formulae youall prvided really worked for me.

 

[sheetspread]

Sheetspread & KavCarter, you guys are genius

Markmzz is the genius (but I'm happy to help all the same )

 

[KevCarter]

Sheetspread & KavCarter, you guys are genius, the formula is just mind blowing. too good. I was using {} formulae which was very complex and file gets hanged. Thank god guys, the formulae youall prvided really worked for me.

Thank you, I'm just really good at copy/paste. We can learn so much from Girvin, Jelen, and everybody on this forum like Mark and SheetSpread!!!

 

[markmzz]

Markmzz is the genius (but I'm happy to help all the same )

Hi Sheetspread!

We're all learning here on the board.

The most import thing is that the user's problem was solved.

Markmzz