index match formatting issue

I am trying to index/match data from a waste code database in an effort to providing a guide for making decisions on correct waste code choice. I downloaded the database from the web but whatever formatting was used I cannot get index match functions to work without physically typing over the entries in the code column

the reasoning i want to explain is that these codes derive from source areas and materials can have different codes depending on source -
the first two numbers = source
second two numbers = sub category
third two numbers = product

any pointers in either changing formatting or rewriting of formulas appreciated (text to columns will not work for some reason)

please see attached workbook

The reason could be your index range, 15 for example, is a numeric number, vs the match 15, is a text. Thus it's not giving you a match.

Another reason is due to trailing spaces. For example in your example of cell G9, 17 08 does not have space, but in your index range, 17 08 is with a trailing space.

i see, so i need to remove all non required spaces from the end of both. Can I trim right to last character?

i used trim to sort data area which sorted some, will look more ta for pointer

I don't know that I have a solid recommendation. Observations:

1) Re: The #N/A error in H5. The lookup value in E5 is the text string "15" (the output of a TRIM() function is text). The value in AE671 (the value I expect is supposed to match "15") is the number 15. The number 15 and the text string "15" are not equal to Excel. H4 correctly finds "10", because the "10" in AE370 is also a "number stored as text", and Excel correctly recognizes the match.
2) Re: The #N/A error in I9. The lookup value in G9 is "17 08". The value in AE810 is "17 08 " (note the trailing space). Obviously these text strings are not equal, so no match is recognized.

There are probably several different solutions. I might start by looking at the importation step for the data in AE:AG. See if there is a better way to force all the numbers to import as text and not numbers. Post-importation, I might try a formula like =TRIM(AE3) copied down in an adjacent column (maybe AH), which will convert numbers to text and get rid of the trailing space that is common in the entries in AE. Then use this column in the MATCH() function. Or some other possibility. Whatever it takes to make sure that the lookup values in column E, F, and G are actually present in the lookup table.