Balance Sheet Preparation.

[Shabbo]

Dear Sir,

Reference to attached sheet,

If group (Column B) is contains Creditors and column “I” is Dr Then column “J” should show as Advance to “Sundry Creditors”.

If group (Column B) is contains Creditors and column “I” is Cr Then column “J” should show as Sundry Creditors”.

If group (Column B) is contains Debtors and column “I” is Cr Then column “J” should show as “Advance from Sundry Debtors”.

If group (Column B) is contains Debtors and column “I” is Dr Then column “J” should show as “Sundry Debtors”.

If group (Column B) is contains Debtors and column “I” is Dr Then column “J” should show as “Sundry Debtors”.

 

[candybg]

Here is one way to do this, using 2 "helper" columns.
Of course the formula could be written without them, but this way you can hopefully understand the formula.

 

[NARAYANK991]

Hi ,

See if this is correct.

Narayan

 

[Chihiro]

Here's another version.

Using Lookup table (in Sheet2) method with INDEX(MATCH()). Formula confirmed as Array (CSE).

If using Excel 2003 or older, use range instead of table structured reference (see column K).