Inbedded Trim in a complex Index Match MMULT formula

MrStern 

Yes, if you're not prepared to cleanse the data within your tables then you're going to have to apply TRIM across all relevant references within your formula. (See edit below: it does not appear that TRIM Is required for the four source tables).  

Personally I'd name all table headers consistently Placement ID: TBL_PLC currently has Placement: Placement as the header, which I'd change so as to align with the other three tables.

Also, although I don't usually recommend volatile solutions over non-volatile ones, perhaps here the reduction in formula length is worth it. As such, I might prefer:

=INDEX(INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT(MyTables&"[#All]"),TRIM([@[Invoice No]])),MyTables)&"[Placement ID]"),IFERROR(MATCH(TRIM([@[Invoice No]]),TRIM(INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT(MyTables&"[#All]"),TRIM([@[Invoice No]])),MyTables)&"[Invoice Number]")),0),MATCH(TRIM([@[Invoice No]]),TRIM(INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT(MyTables&"[#All]"),TRIM([@[Invoice No]])),MyTables)&"[Credit Note Number]")),0)))

where MyTables is defined within Name Manager, viz:

Name: MyTables

Refers to: ={"TBL_PLC";"Tbl_Logix_CM";"Tbl_Logix_Prior";"Tbl_Logix_Cutoff"}

However, I understand if you want to retain your original set-up, which, with the required TRIM functions, would be:

=IFERROR(IFERROR(IFERROR(IFERROR(INDEX(TBL_PLC[[#All],[Placement ID]],MATCH(1,MMULT(--(TRIM(TBL_PLC[[#All],[Invoice Number]:[Credit Note Number]])=TRIM([@[Invoice No]])),TRANSPOSE(COLUMN(TBL_PLC[[#All],[Invoice Number]:[Credit Note Number]])^0)),)),INDEX(Tbl_Logix_CM[[#All],[Placement ID]],MATCH(1,MMULT(--(TRIM(Tbl_Logix_CM[[#All],[Invoice Number]:[Credit Note Number]])=TRIM([@[Invoice No]])),TRANSPOSE(COLUMN(Tbl_Logix_CM[[#All],[Invoice Number]:[Credit Note Number]])^0)),))),INDEX(Tbl_Logix_Prior[[#All],[Placement ID]],MATCH(1,MMULT(--(TRIM(Tbl_Logix_Prior[[#All],[Invoice Number]:[Credit Note Number]])=TRIM([@[Invoice No]])),TRANSPOSE(COLUMN(Tbl_Logix_Prior[[#All],[Invoice Number]:[Credit Note Number]])^0)),))),INDEX(Tbl_Logix_Cutoff[[#All],[Placement ID]],MATCH(1,MMULT(--(TRIM(Tbl_Logix_Cutoff[[#All],[Invoice Number]:[Credit Note Number]])=TRIM([@[Invoice No]])),TRANSPOSE(COLUMN(Tbl_Logix_Cutoff[[#All],[Invoice Number]:[Credit Note Number]])^0)),))),"")

Edit: after further study, it appears that the entries in the four separate tables do not in fact require trimming. However, since TRIM coerces numbers to text, it will still be necessary to coerce the values in those tables into text in order for MATCH to recognise them. By a lucky coincidence, TRIM will actually do this (i.e. convert numbers to text), so the above solution will work; however, the use of TRIM to coerce numbers to text is abstruse: the following version is more logical:

=IFERROR(IFERROR(IFERROR(IFERROR(INDEX(TBL_PLC[[#All],[Placement ID]],MATCH(1,MMULT(--(""&TBL_PLC[[#All],[Invoice Number]:[Credit Note Number]]=TRIM([@[Invoice No]])),TRANSPOSE(COLUMN(TBL_PLC[[#All],[Invoice Number]:[Credit Note Number]])^0)),)),INDEX(Tbl_Logix_CM[[#All],[Placement ID]],MATCH(1,MMULT(--(""&Tbl_Logix_CM[[#All],[Invoice Number]:[Credit Note Number]]=TRIM([@[Invoice No]])),TRANSPOSE(COLUMN(Tbl_Logix_CM[[#All],[Invoice Number]:[Credit Note Number]])^0)),))),INDEX(Tbl_Logix_Prior[[#All],[Placement ID]],MATCH(1,MMULT(--(""&Tbl_Logix_Prior[[#All],[Invoice Number]:[Credit Note Number]]=TRIM([@[Invoice No]])),TRANSPOSE(COLUMN(Tbl_Logix_Prior[[#All],[Invoice Number]:[Credit Note Number]])^0)),))),INDEX(Tbl_Logix_Cutoff[[#All],[Placement ID]],MATCH(1,MMULT(--(""&Tbl_Logix_Cutoff[[#All],[Invoice Number]:[Credit Note Number]]=TRIM([@[Invoice No]])),TRANSPOSE(COLUMN(Tbl_Logix_Cutoff[[#All],[Invoice Number]:[Credit Note Number]])^0)),))),"")

Regards