Complicated formula help :(

[PSI94]

Happy Sunday folks!

I can't get my head around how to write this formula, so any help would be greatly appreciated. So, this is the data I'm working with -

Data set 1)

Fiscal year/period	Input Id	Book Indicator	Reins Cat. SAP Short	Company	Organizational Unit	Geographic Location	Statistical State	Financial Product	Product/Coverage	Convention Line	Profit Center	Reinsurance Company	Report Date	Accident Date	Allocated Expense Type	Channel of Bind	Catastrophe Code	Mold Indicator	Policy Option Package	Alloc Expense Paid	Allocated Expense by Type	Case Expense Reserves	Case Loss Reserves	Closure - Exp Only Count	Closure - Loss + Exp Count	Closure - Loss Only Count	Closure - No Pay Count	Closure Expense Amount	Closure Loss Amount	Gross Paid Loss	IBNR Expense	IBNR Loss	Notice Counts	Pending Count	Salvage	Subrogation	Supplemental Expense Reserves	Supplemental Loss Reserves	Unalloc CDE Reserves
001.2022	12MC	M	DB	330	2	IL	12	0000000100	0010	1911	A001/100ILTG	#	01.06.2021	01.06.2021	#	001	#	0	#	0	0	0	2596	0	0	0	0	0	0	0	0	0	0	1	0	0	0	6490	0
001.2022	12MC	M	DB	330	2	IL	12	0000000100	0020	2001	A001/100ILTG	#	01.07.2021	01.07.2021	#	001	#	0	#	0	0	0	6000	0	0	0	0	0	0	0	0	0	0	3	0	0	0	0	0
001.2022	12MC	M	DB	330	2	IL	12	0000000100	0030	1921	A001/100ILTG	#	01.05.2021	01.05.2021	#	001	#	0	#	0	0	0	1000	0	0	0	0	0	0	0	0	500	0	1	0	0	0	0	0

Formula for first cell of the data -

=IFERROR(INDEX(XWH!B:B,MATCH(Key!$AB2,XWH!$A:$A,)), "-")

Data Set 2)

XWH Name	Transaction Type	Account + Trans Type	Company Code	Reference	User name	Document Type	Account	Transaction Type	General ledger amount	Profit Center	Financial Product	Coverage	Statutory Product	Geographic Location	Reinsurance Category	Reinsurance Company	Premium/Commision Type	Policy Term	Functional Area	Transact. type	Trading partner	Posting Period	Entry Date
0	Reported Loss Reserves	M20000000DEV	330	12MC0601	XIFPBT010	Y8	M20000000	DEV	-5192	100ILTG	0000000100	0010	1911	IL	DB	0	0	006	0	0	0	1	11/03/2022
0	Reported Loss Reserves	M20000000FLD	330	12MC0601	XIFPBT010	Y8	M20000000	FLD	-2000	100ILTG	0000000100	0020	2001	IL	DB	0	0	006	0	0	0	1	11/03/2022
-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-

Formula for first cell of the data -

=IFERROR(INDEX(FICO!B:B,MATCH(Key!$AB2,FICO!$A:$A,)), "-")

These two data sets are side by side on one worksheet, it was just easier to show the distinction between the two. These are pulled through from two seperate worksheets with a unique key I created - an example is 00101911 - this is made up of Product/Coverage & Convention Line from data set 1, and Coverage & Statutory Line from data set 2.

My issue is that within data set 2, the general ledger amount relates to the XWH name (from data set two) which relates to the values in data set 1 from allocated expense, onwards. So this changes from line to line. I built the XWH formula in data set 2 so that it pulls the names from data set 2 but i need it to then match the other data set - on the same line as data set 1. I dont know whether this will involve building a second key or?

I then need add to data set 2 the column names from data set 1, so then the relevant data populated under the relevant heading. If that makes sense. So for example, if the first line of the general ledger amt in data set 2 relates to gross paid loss of data set 1. I then need it to populate under the newly created column.

Sorry I know this is v confusing and I'm not sure if there's actually a way. Any help would be greatly appreciated.

 

[Fluff]

Duplicate to: Complicated formula help :(

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.