VLookup evaluating a formula field returns N/A

[cosmit]

I have a spreadsheet that has one tab that includes 2 columns: student ID and grade. Some student IDs are prefaced with a "w" and some are not.
The same spreadsheet has another tab with 2 columns: student ID and student name. None of the student IDs are prefaced with a "w"

I am trying to do a vlookup on the student ID on the first tab so that the student name is returned from the 2nd tab to a field on the 1st tab.

So that all of the student IDs are the same format, I applied this formula to remove the "w" from the student ID on the first tab: =MID(A13,2,10).

My vlookup works great on the rows that already existed without the "w". But on the cells that have the formula above, it returns N/A. When I evaluate the formula, it seems like it is finding the right student ID (without the "w") but it still returns N/A.

Any help would be appreciated.

[Pete_UK]

When you use MID on those IDs with a "w", the formula will return a text value that looks like a number. You can convert it to an actual number in several ways, e.g.:

=MID(A13,2,10)+0

=MID(A13,2,10)*1

=--MID(A13,2,10)

=VALUE(MID(A13,2,10))

VLOOKUP will not match a number with a text value that looks like a number, so you have to ensure that all the lookup values have the same format type.

Hope this helps.

Pete

[cosmit]

Genius!!! That worked perfectly! Thank you!

[Pete_UK]

You're welcome - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you and show your appreciation of their efforts by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete