Excel - Value Not Available Error

Question

Hello,

I have three tables that I use for tracking physical fitness scores;

Male, Female, and Co-ed tables.

The male and female tables with the VLOOKUP are working fine.

But in the Co-ed table I keep getting the return #N/A in some of the cells.

Answer 1

Hi Jesus,

For #N/A error of VLOOKUP function, you could refer to How to correct a #N/A error in the VLOOKUP function.

In the meantime, Evaluate Formula feature might be also helpful for checking the process.

If the issue persists when you check via the methods above, I would also like to collect the issued workbook via Private Message.

Regards,

Eli

Answer 2

Hi Jesus,

According to MATCH function, I found the value cannot be matched in the reference range so that there were lots of #N/A error. I try modifying the reference format and use the value from reference. It can be fixed now. I have sent the workbook to you via PM. Please take a look.

Regards,

Eli

Answer 3

Hi Jesus,

May I know if you have checked my copy in PM?

Regards,

Eli

Answer 4

Morning,

I did get to check the copy. But, once I input a time for the run again I received an error again. 

I don't know what is causing it.

Thank you,

J.Sanchez

Answer 5

Hi Jesus,

Thank you for writing back.

I think the root cause is the format of the reference of data. As you mentioned, the error #N/A will appear again when you type the number manually. Because the number you typed is different from the number in the reference. To do a simple test by yourself, steps as below:

1.Type 3:05 in J133 cell manually

2.Get the error #N/A

3.Find the corresponding reference data in Male table. It's C235 cell

4.Type 3:05 in C235 cell manually

You will find the #N/A will disappear. It proves that the reference of data is based on a type of special format, even if using Format Painter to correct it. So if it also works on your end, the best solution to address this issue is rebuild the reference.

For any concerns, feel free to post back.

Regards,

Eli