VLOOKUP fails when lookup cell isn't entered manually but retreived from named range

[sprunknwn]

So I have a table with player names and their stats (points, rebounds, etc).

Now, I have been inputting the names under the Name column manually.
But, I now created a query to retreive the player names for each team and created a named range for that list of names.
I put the named range in a column outside the table (because I cant populate a named range inside a table for some reason) and I can see the list of player names.
Then I want to reference(or populate) those names from that column to the "Name" column.
When I do that, it does take the names and puts them in the "Name" column in order but it messes up the stats retreival.
I use VLOOKUP according to the player name to retreive stats from another workbook that has all the player stats for the entire league.

=IFERROR(VLOOKUP(B2,'C:\[stats.xls]sheet1'!$C$2:$R$455,7,FALSE),0)*C2

This is to retreive the 3PM values for example.
But when the player name is not put manually but retreive from a side column using a named range, I get all zeros, as if it can't find the player name.

In this case I just did -

=P2

In the B2 cell and as you can see, it does put the player name there but all the VLOOKUP formulas, fail to retreive the stats for that name.

What am I doing wrong/missing here?

Any help would be appreciated, been trying so many ways to retreive the names and getting zero values each time.

Thanks in advance!

 

[arthurbr]

Nice example of the dangers of the IFERROR function. You get 0 everywhere and you don't know why. Removing the IFERROR will probably yield NA's everywhere.
So your lookup value has not been found, probably because the value from the named range has a trailing space or something ( eventually "dirty data"?). Check the length of the lookup value and correct as necessary. Wrap the lookup value within the TRIM function perhaps
I for one do not use IFERROR as it hides ALL errors

 

[sprunknwn]

Nice example of the dangers of the IFERROR function. You get 0 everywhere and you don't know why. Removing the IFERROR will probably yield NA's everywhere.
So your lookup value has not been found, probably because the value from the named range has a trailing space or something ( eventually "dirty data"?). Check the length of the lookup value and correct as necessary. Wrap the lookup value within the TRIM function perhaps
I for one do not use IFERROR as it hides ALL errors

I removed the IFERROR and indeed I had #N/A.
Then I used TRIM on the original formula creating the name in the named range.
Use the same simple =P2 call to my table name but am still getting #N/A.

What else could be wrong?

Thanks a lot for your feedback, it's much appreciated.

 

[arthurbr]

Like this ?
=VLOOKUP(TRIM(B2),'C:\[stats.xls]sheet1'!$C$2:$R$455,7,FALSE)*C2

 

[sprunknwn]

Like this ?
=VLOOKUP(TRIM(B2),'C:\[stats.xls]sheet1'!$C$2:$R$455,7,FALSE)*C2

Yea, tried it on the actual cell in the VLOOKUP, I even tried without the VLOOKUP, just putting the named range in a column next to the table and just = to it.
Tried that with TRIM and without, still won't go for some reason.
The only way the VLOOKUP works is if I enter the name of the player manually.
Can't seem to get it to work when its' referencing another Cell unless the other cell was also entered manually.

 

[offthelip]

Have alook at this thread, it could be the cause of your problem:
identify character that looks like a space

 

[sprunknwn]

Thanks offthelip.
Your lead sent me in the right path to find all the chars in the cell and from there I saw I had a 13,10 before the name and another space (32) at the end of it.
Once i substituted those out, it worked!

Cheers!