Forum Discussion
Inbedded Trim in a complex Index Match MMULT formula
I am working on setting up a reconciliation, and I need to lookup Placement numbers on the reconciliation.
I am looking up against the invoice no., and this is index matched against four different tables to find the placement number.
When I enter the formula in my reconciliation, it does for the most part successfully look up the result from the four tables.
However in other instances, the result does get identified.
If I were to trim the data in the reconciliation, it will successfully lookup the missing data, however conversely the data that was looked up will no long be found.
I can trim data in the reconciliation, however the Tables that I am looking up will not support this.
I have tried to integrate the trim into my lookup formula, however had not had success.
Any help would be appreciated.
{=IFERROR(IFERROR(IFERROR(IFERROR(INDEX(Tbl_PLC[[#All],[Placement: Placement]],MATCH(1,MMULT(--(Tbl_PLC[[#All],[Invoice Number]:[Credit Note Number]]=[@[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]]=[@[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]]=[@[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]]=[@[Invoice No]]),TRANSPOSE(COLUMN(Tbl_Logix_Cutoff[[#All],[Invoice Number]:[Credit Note Number]])^0)),))),"")}
5 Replies
- This makes it difficult to use VLOOKUP to find a value based on more than one criteria. However, if you have control over the source data, you an add a helper column that concatenates 2 more more fields together, then give VLOOKUP a lookup value that does the same.
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
Thanks That worked a treat!
You didn't provide any explanation. So I made some assumptions.
Solution is done with Power Query.
