VLOOKUP Function not working properly

Hello All,

I would like some help with the VLOOKUP function....

I am trying to use the VLOOKUP function in Excel to display the TOP Code of a course. In the TOP Code column in Sheet 1, I want to find a course that exists in Sheet 1 and Sheet 2, then display its corresponding TOP Code from Sheet 2 in Sheet 1.

In the attached file, I made two VLOOKUP function formulas that do not seem to be working properly. The one next to "ACCT 001" omits TRUE/FALSE and therefore gives an incorrect TOP code of "0502.10* Tax Studies." It should be "0502.00* Accounting."

In the second VLOOKUP function formula, I added FALSE to look up an exact match of "ACCT 002" in Sheet 2 but the result is a #N/A error.

I also tried the following INDEX/MATCH function formula but it gave me similar incorrect results: =INDEX(Sheet2!B:B,MATCH(Sheet1!A6,Sheet2!A:A))

I am not sure what I am doing wrong. Any help is greatly appreciated.

On sheet 1 you have things like BUS 119, but on sheet2 it's BUS119 without the space

Also on sheet 1 some of the spaces are non-breaking spaces, rather than normal spaces.
To fix this use Ctrl H > in the Find what type Alt 0160 (you must use the numeric keypad, not the numbers on the main keyboard) > In Replace put a space > replace All

Hi

In sheet 1 you must change all code 160 by 32

In Sheet 2 ACCT 001 has codes 65,67,67,84,32,48,48,49
In Sheet 1 ACCT 001 has codes 65,67,67,84,160,48,48,49

Also some of the value in col A on sheet2 have a space after them

Thank you so much to all who helped!

ACCT 001 in both sheets seemed to match. Now that I know that the space in between caused them not to match I was able to replace all the spaces. I will also work on fixing the data in Sheet 2 to have proper spacing between the subject and the course number.

This truly is going to save me a tremendous amount of time and effort. Thanks again and have a great day. This is an awesome forum!

You're welcome & thanks for the feedback