Problems trying to match criteria in lookup queries

If the numbering in the lookup table and the lookup criteria are consistent then there is no obvoius reason why it should not work with exact match, that is of course assuming that there is a match within the table.

Because of the format your numbers are actually text strings not proper numbers, so that could be part of the reason why approx is not working as expected.
Also bear in mind that the lookup table will need to be sorted in ascending order for approx match to work.

If that doesn't help then a sample workbook (with fictional data that accurately represents your real data) will be required so that we can see exactly what you have and how excel is interpreting the numbers.

To attach a file, hit 'Go Advanced' then scroll down and look for the 'Manage Attachments' text link (not the paper clip icon, that is broken).

Here is a sample I whipped up - hope it illustrates the point. Thank you.

Did you copy your data from a web page?

The source table contains non breaking spaces, which are not the same as the space that you type with the keyboard.

On the MFDivisions sheet, press Ctrl h

Make sure that the 'Find What' box is completely empty, then hold the Alt key and type 0160 on the number pad (doesn't work with the numbers on the top row of the keyboard).
Enter a normal space with the spacebar into the 'Repalce With' box, then hit 'Replace All'

Now VLOOKUP and INDEX / MATCH should work properly with exact match.

In D6, =LEFT([@Division],2)&" 00 00"
In E6, =VLOOKUP([@[MF Level 1]],MFdivisions!D4:E6494,2,0)

Hopefully you can figure out the other 2 from that, if not, shout for help

Thank you! that worked wonderfully! I didn't realize the spaces were off, I had gotten the source data from someone else. I will have to add this trick to my toolbox. Thank you so much!