near exact match between different sheets

Yes, but without seeing some sample data, it's hard to advise exactly how.

Will you please attach a sample Excel workbook? Not a picture of one!

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

See if this is helpful, it works for the sample data.
Column E on sheet 4 is populated using: =TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",LEN(B2)),2),LEN(B2)))
Columns K:N on sheet 2 are populated using:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

thanks for your help . It works great, is it possible that when a match is found that it cuts the information from sheet 4. So that all that will be left on sheet 4 are those that don't match?

You're Welcome and thank you for the feedback. As to cutting information from sheet 4 a formula won't do that, you'd need VBA (I am illiterate when it comes to code). You might consider marking this thread (and the one on the MrExcel site) as 'Solved' since we found a way to match, and open a new thread on the Programming/VBA/Macros forum, on this site, asking for help in deleting rows from sheet 4 but leaving the matched information on sheet 2. I hope that you have a blessed day.