Long array formula

[drag1c]

Hi,

I am trying to create .arrayformula from this:

Sheets("Procurement report").Range("AC19").FormulaArray = "=IFERROR(INDEX(CommentsTable[Comment]:CommentsTable[Comment],MATCH(1,(CommentsTable[Purchase Requisition]:CommentsTable[Purchase Requisition]=B19)*(CommentsTable[Item of requisition]:CommentsTable[Item of requisition]=C19),0)),"""")"

I've did this:

longformula1 = "=IFERROR(INDEX(XXX:XXX,MATCH(1,(CommentsTable[Purchase Requisition]:CommentsTable[Purchase Requisition]=B19)*(AAA:AAA=C19),0)),"""")"
    longformula3 = "CommentsTable[Item of requisition]:CommentsTable[Item of requisition]"
    longformula2 = "CommentsTable[Comment]:CommentsTable[Comment]"
    
    With Sheet1.Range("AC19")
    
    .FormulaArray = longformula1
    .Replace "XXX:XXX", longformula2
    .Replace "AAA:AAA", longformula3
    
    End With

But it doesn't work.
Could anyone check?

P.S. My formula has total 235 chars. I don't understand why it doesn't work. Could it be written shorter?

 

[Fluff]

How about

Sheets("Procurement report").Range("AC19").FormulaArray = "=IFERROR(INDEX(CommentsTable[Comment],MATCH(1,(CommentsTable[Purchase Requisition]=B19)*(CommentsTable[Item of requisition]=C19),0)),"""")"

 

[drag1c]

 

[drag1c]

Sorry for double post. I've solved it.
Table from which data was pulled did not have the same name of header.
Your solution works.

Could you just explain me difference if no problem?

 

[Fluff]

Check that the table & column names are correct.

 

[drag1c]

Fluff actually there is a problem, data is not listed correct with this formula.

 

[Fluff]

If the formula is not doing what you want, then that is a totally different question & needs a new thread.

 

[drag1c]

Fluff, it works. There is just mess with data from database. In my example there is no unique ID, therefore data is wrong

Thank you for help !

 

[Fluff]

You're welcome & thanks for the feedback.