Finding and removing specific characters

Hi all,

Still chipping away at reconciling two sets of data. This time I know that there are matches between data but one set of data has a prefix of 'I-' and the other doesn't so MATCH can't find. I've attached a small example. In sheet one are 15 invoices and in sheet two are the exact same invoices but this time prefixed with 'I-' and contains information about cost. I want sheet one show me the cost of the corresponding invoice on sheet two. I'm happy with the function in column B of sheet one.

Do I need to use a function in Column A of sheet 2? I'm thinking there might be something I could use where the logic is 'if 'I-' appears in column A then remove it or substitute it for ""'

The thing to note here is that my two sets of data won't contain only this kind of invoice with an I- prefix as other suppliers invoices appear on the system and so using something like the LEFT function won't work.

Thank you!

Thanks Bo_Ry

Try this in B2:

=INDEX(Table2[Cost],MATCH("*"&A2,Table2[Column1],0))

Thanks 63falcondude

Yes, Forum Guru this is it. It resolves the issue even with invoices with different structures. Thank you! SOLVED