VLookup doesn't recognize numbers, only text

Kelley B

I am having an issue with Vlookup not returning numbers (will return text). Previous users have reported similar issues but previous suggestions are not working for me. My original code is:

=VLOOKUP([Project #]@row, {projectMASTER Range 1}, 2, false) --> it returns text (ie, 40100222-64) but not numbers (ie. 40100222) -- See below:

I have tried:

=VALUE(VLOOKUP([Project #]@row, {projectMASTER Range 1}, 3, false))

=IFERROR(VLOOKUP([Project #]@row, {projectMASTER Range 1}, 2, false), VLOOKUP(VALUE([Project #]@row, {projectMASTER Range 1}, 2, false)))

The only thing that I have found to work is to manually enter the numbers. However, I have over 3000 records, more than half of which are numbers, so this doesn't seem efficient or feasible.

HELP!! 😣

Mike TV

@Kelley B

The formula =VLOOKUP([Project #]@row, {projectMASTER Range 1}, 2, false) worked for me. You've got your cross-sheet reference set up like this, right?

I see in your post you mention that this works for you if you enter the Project #s manually. Since I was creating a test sheet to test your formula, I of course entered them manually which maybe is why it works for me and not for you. So how are your Project #s making it onto your sheet that's causing them to be treated differently?

An alternative that will probably work for you would be to switch to INDEX MATCH instead of VLOOKUP. You'll just have to set up 3 cross-sheet references (or more if there are more columns you need to map in). At least you won't have to re-enter 3000 Project #'s though.