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]

Changing the A5 in the formulas below to the cell that contains the 8 in the sheet Ranking 1 column A what does it return?

=ISNUMBER(A5)

=EXACT(A5,8)

and what do you get if you do the same in the sheet with the vlookup with

=ISNUMBER(AE3)

and

=EXACT(AE3,8)

 

[KevinZ]

Sorry, read the last post wrong.

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


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


return?

These return the correct values for the number (1, 2, 3 etc) but do not return the correct values for the non number (T1, T2, T3 etc) or #N/A.

ISNUMBR and EXACT return True or False in both the Ranking sheet and the Main sheet.
​

 

[MARK858]

ISNUMBR and EXACT return True or False in both the Ranking sheet and the Main sheet.

Need them listed for the individual questions

These return the correct values for the number (1, 2, 3 etc)

So they returned the correct value for the initial question of the returned N/A which was the AE3 with the "8"?

or #N/A

Do not understand, you are not looking up an #N/A are you?

 

[KevinZ]

I'm not sure I follow on the ISNUMBER and EXACT usage.

For the initial question, Yes for a whole number but the values that can be returned can be either a whole number (1, 2, 3, etc), an alpha numeric value (T1, T2, T3, etc) or #N/A. They are all possible values that can be returned and will change on a weekly basis. So because teh value is 8 right now, it could be T8 next week or it could be 25, T1, N/A etc.

 

[MARK858]

I'm not sure I follow on the ISNUMBER and EXACT usage.

I need specific answers for the specific cells I quoted in the post individually.

You should have 4 results

=ISNUMBER(on sheet Ranking 1 cell in column A containing 8)
=EXACT(on sheet Ranking 1 cell in column A containing 8,8)

and on the main sheet

=ISNUMBER(AE3)
=EXACT(AE3,8)

I need to see all 4 results

They are all possible values that can be returned

I am only interested at the moment in cells in the look up and in column A sheet Ranking 1 that are returning incorrect results. This why I am concentrating on the values in AE3 and the cell in column A of sheet Ranking 1 that has the 8.

 

[MARK858]

@KevinZ, do you get the correct results with

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

 

[KevinZ]

That has corrected all but the #N/A value. I have assigned a value of 0 in the Ranking 1 spreadsheet for #N/A. I tried #N/A and "#N/A" in the Ranking spreadsheet but it does not bring the 0 value back. Other than that, the other values work great.

 

[Fluff]

I would recommend changing the #N/A values to N/A, otherwise xl will read your values as errors

 

[KevinZ]

I have changed the #N/A to N/A on the Ranking 1 spreadsheet, The VLOOKUP still does not return a value of 0. It is till displaying a value of #N/A with the Value not available error.

To recap, The cells I am looking at have the following VLOOKUP.

=VLOOKUP(ZW3,'F:\Fantasy Sports\Research\Golf Templates\[Fantasy Golf Sheet 4 1-14-19.xlsx]Scoring Avg'!$A:$D,2,FALSE).
This is returning #N/A as the value since there is nothing on the underlying spreadsheet for this particular lookup.

In a new cell I now have =IF(ISERROR(VALUE(AF14)),VLOOKUP(AF14,'Ranking 1'!A:B,2,FALSE),VLOOKUP(AF14+0,'Ranking 1'!A:B,2,FALSE)) as provided by Mark858. This is brining the correct values when the lookup finds a number (1,2,3) or an alphanumeric (T1,T2,T3). It does not find the correct value when the first cell has a value of #N/A.

The #N/A can be in any of the cells from the first one as this is a list that gets updated weekly with a varying list of lookups.
​

 

[Fluff]

Change you formula to
=IFERROR(VLOOKUP(ZW3,'F:\Fantasy Sports\Research\Golf Templates\[Fantasy Golf Sheet 4 1-14-19.xlsx]Scoring Avg'!$A:$D,2,FALSE),"N/A")