VLOOKUP Not returning proper values

[KevinZ]

I currently have a spreadsheet that aggregates values from other spreadsheets using VLOOKUPS. On this aggregated spreadsheet, I have numerous columns that I want to add a custom value to in a separate column. The values returned from the underlying spreadsheets are #N/A, 1, 2, 3, T1, T2, T3, etc. These can be up to approx. 1000 or T1000. I am using a VLOOKUP to create a custom value #N/A = 0, 1 to 10 = 1, T1 to T10 = 1, 11 to 20 = 2, T11 to T20 =2, etc. I have created a separate spreadsheet with the values I have assigned. Note, Each call will only have 1 value.

I am getting a Value not Available error on the cells.
For example, I have a value of 8 in a target cell.
The formula evaluation appears to be correct: VLOOKUP("8",'Ranking 1'!A:B,2,False)
In the Ranking 1 spreadsheet, I am assigning a value of 5. The VLOOKUP is displaying the error and a #N/A in the field.

 

[MARK858]

What happens if you remove the quotes around the 8?

 

[Fluff]

Are the values in the Ranking sheet text or numers?
If numbers remove the quotes from "8"

 

[KevinZ]

The quotes around the 8 a created by the VLOOKUP. It is the value that is pulled in from one of the underlying sheets.

 

[sandy666]

deleted

forgot to refresh thread

 

[KevinZ]

The values on the ranking spreadsheet are numbers, I hand typed them, no quotes.

 

[MARK858]

The quotes around the 8 a created by the VLOOKUP. It is the value that is pulled in from one of the underlying sheets.

I didn't ask that, I asked what happens when you remove them. Test and respond.

 

[KevinZ]

To Clarify
The field I am looking up has this in AE3: =VLOOKUP(ZW3,'F:\Fantasy Sports\Research\Golf Templates\[Fantasy Golf Sheet 4 1-14-19.xlsx]Scoring Avg'!$A:$D,2,FALSE). This returns a value of 8 in the spreadsheet.

The new field is using this: =VLOOKUP(AE3,'Ranking 1'!A:B,2,FALSE).
The Error code has this: Reference: 'Combined Stats'!$AD$3 = Evaluation:

VLOOKUP("8",'Ranking 1'!A:B,2,False)​

 

[MARK858]

What does

=VLOOKUP(VALUE(AE3),'Ranking 1'!A:B,2,FALSE)

or

=VLOOKUP(AE3+0,'Ranking 1'!A:B,2,FALSE)

return?

 

[KevinZ]

It returns #N/A with the error that a Value is not available.