Complicated formula help :(

Status
Not open for further replies.

PSI94

New Member
Joined
Mar 11, 2022
Messages
23
Office Version
  1. 2021
Platform
  1. Windows
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/periodInput IdBook IndicatorReins Cat. SAP ShortCompanyOrganizational UnitGeographic LocationStatistical StateFinancial ProductProduct/CoverageConvention LineProfit CenterReinsurance CompanyReport DateAccident DateAllocated Expense TypeChannel of BindCatastrophe CodeMold IndicatorPolicy Option PackageAlloc Expense PaidAllocated Expense by TypeCase Expense ReservesCase Loss ReservesClosure - Exp Only CountClosure - Loss + Exp CountClosure - Loss Only CountClosure - No Pay CountClosure Expense AmountClosure Loss AmountGross Paid LossIBNR ExpenseIBNR LossNotice CountsPending CountSalvageSubrogationSupplemental Expense ReservesSupplemental Loss ReservesUnalloc CDE Reserves
001.202212MCMDB3302IL12000000010000101911A001/100ILTG#01.06.202101.06.2021#001#0#00025960000000000100064900
001.202212MCMDB3302IL12000000010000202001A001/100ILTG#01.07.202101.07.2021#001#0#00060000000000000300000
001.202212MCMDB3302IL12000000010000301921A001/100ILTG#01.05.202101.05.2021#001#0#0001000000000005000100000

Formula for first cell of the data -

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

Data Set 2)


XWH NameTransaction TypeAccount + Trans TypeCompany CodeReferenceUser nameDocument TypeAccountTransaction TypeGeneral ledger amountProfit CenterFinancial ProductCoverageStatutory ProductGeographic LocationReinsurance CategoryReinsurance CompanyPremium/Commision TypePolicy TermFunctional AreaTransact. typeTrading partnerPosting PeriodEntry Date
0Reported Loss ReservesM20000000DEV33012MC0601XIFPBT010Y8M20000000DEV-5192100ILTG000000010000101911ILDB00006000111/03/2022
0Reported Loss ReservesM20000000FLD33012MC0601XIFPBT010Y8M20000000FLD-2000100ILTG000000010000202001ILDB00006000111/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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,771
Office Version
  1. 365
Platform
  1. Windows
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.
 
Status
Not open for further replies.

Forum statistics

Threads
1,176,264
Messages
5,902,231
Members
434,952
Latest member
AlanN

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
Top