Vlookup #n/a

[oalkhalili]

Hello,
I have to use index with match functions to find the correct cell in the matrix grade range A2:F16. I had tracked the error by a simple VLOOKUP. Below image shows the vlookup I am trying to use to find the result values for I19, based on the the range B20:C30. The vlookup works on all percentages except 6%, 10% and 11%; I receive #N/A error. I could not figure out why.
I hope someone can help with this.
Thank you,

 

[Jonmo1]

If that vlookup returns #N/A, it can only mean that there is no 'Exact' match for I18 within B20:B30.

Is I18 a formula?
What about the value sin B20:B30, are they formulas or manual entry?

Maybe I18 and/or B25(your expected matching cell) isn't 'EXACTLY' 6%
It could be 6.01%, or even 5.99%, but cell formatting has it set to show 0 decimals, so it appears rounded to 6%

Format I18 and B20:B30 to show 15 decimals, see if any of them show a difference.

 

[gaz_chops]

Welcome to the forum.

Are the values in Grade actually 6% etc. not 6.1% but not displayed?

 

[oalkhalili]

All entries are statistic. I am not using calculation for the lookup value

 

[gaz_chops]

All entries are statistic. I am not using calculation for the lookup value

Did you try what Jonmo1 suggested?

"Format I18 and B20:B30 to show 15 decimals, see if any of them show a difference."

 

[oalkhalili]

Thank you for responding. All entries are entered manually; the percentages do not have decimals.

 

[gaz_chops]

Thank you for responding. All entries are entered manually; the percentages do not have decimals.

Then the formula should work, is it possible for you to upload a sample file to dropbox or similar, then post the link here.

 

[Jonmo1]

Maybe some of them are entered as TEXT instead of Number..

What do these return

=ISNUMBER(I18)

and
=ISNUMBER(B20)
repeated through B30

?

 

[oalkhalili]

The return is all TRUE

 

[viktiw]

Either one of the cell values is not in percentage format.
Its' possible that one could be in percentage format and other could be in Text format.

Check for this and the decimal places. I think it will work.

viktiw