2 variable index match with wildcard

wondering if anyone can help me out with this one pretty stuck.

I have 2 tabs on my worksheet:
Index tab: contains the info I need to extract

On sheet1 I have partial invoices that contain some only numeric values and some alpha numeric. The second criteria I need to match are the amounts highlighted in yellow.

The tricky part is with the partial invoices I need to find the numeric part of the partial invoice and compare it to the invoice/claim number on the index tab. once it finds matches I need it to compare the second criteria which is the posamount from sheet1 to index exact values and return the full invoice number on column A of index tab.

Who ever could figure this one out is really a genius

thanks guys!

report.xlsx

Put this array* formula in H4:

=IFERROR(INDEX(Index!$A$2:$A$1600,MATCH("*"&C4&"*"&E4,Index!$A$2:$A$1600&Index!$F$2:$F$1600,0)),"")

then copy down to the bottom of your list. You will need to scroll down to find some matches.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual <Enter>.

Hope this helps.

Pete

it seems to be working the only thing is that when an invoice or partial invoice number on column C contains non numeric values it does not ignore them and does not only try to match the numeric values

Can't you arrange it so that only numbers can be typed in to column C? You could use Data Validation on column C to ensure this.

Hope this helps.

Pete

thanks will try that or run a separate macro to clean up any non numeric values