In excel, I am trying to do a VLOOKUP or INDEX/MATCH on a column which contains some numbers and some strings of mixed numbers and letters. It looks something like this.

In a separate sheet, I have the values in column one and I need to bring in the corresponding values for column 2. This should be very simple to do with this code: =INDEX(Sheet1!$B$1:$B$6, MATCH(Sheet2!$A1,Sheet1!$A$1:$A$6))

And indeed, in this toy example it works as expected. You can see I converted all numbers to text using the dropdown menu on the home screen. The problem is, in my real life work example, I can't make the program recognize all the numbers in sheet 2 as text.

In sheet 1, after changing the datatype to 'text', all of the cells with numbers have a little green triangle and when you select a cell it gives a yellow exclamation point warning you that it is a number formatted as text, great. But in sheet 2, with the exact same type of data, the green triangle and yellow warnings do not appear. And as a result, my VLOOKUP or INDEX/MATCH (I tried both) return N/A when there should be a match. If I go into one of these number cells and just click the cursor, it will get a green triangle and the formula will work for that cell (like I forced it to recognize it as text). But I can't do that for thousands of cells. Any advice is greatly appreciated. -Ben