Excel Index Match Across another workbook whilst using RIGHT and Trim function?

Question

I have a workbook Audit Numbers.xlsm

Column P contains a list of addresses like so:

Address Line 1, Sutton, SM2 78H
Address Line 2, Mitcham, M1 2NN
Address Line 3, Manchester, M15 4GS

In my other workbook, where my formula is going to be, i have column I with just the postcode of an address:

SM2 78H
M1 2NN
M15 4GS

I want to try and use index match to retrieve a value from column I in the audit numbers workbook if the postcode matches. I am using the below formula:

=INDEX('[Audit Numbers.xlsm]Supplier'!$I:$I,MATCH(I4,TRIM(RIGHT(TRIM(RIGHT(SUBSTITUTE('[Audit Numbers.xlsm]Supplier'!$P:$P,",",REPT(" ",LEN('[Audit Numbers.xlsm]Supplier'!$P:$P))),LEN('[Audit Numbers.xlsm]Supplier'!$P:$P))),8))))

But i get a #Value error. Please can someone show me where i am going wrong?

Answer 1

I'd do it (something) like this instead:

=INDEX(A1:A3,MATCH(1,IFERROR(--(FIND(A6,A1:A3)>0),0),0))

to enter the function press Ctrl+Shift+Enter (rather than just Enter)

---

(I've placed the formula above in A7)