VLOOKUP #N/A Error

[taliesin42421]

Hi all. I am at my wit's end trying to figure out this error. I am building a scoring template for another test. There is an input sheet for demographic information and Raw Time in Seconds. The Raw Time in Seconds provides T-scores based on the examinee's age. The T-Scores and Percentile Ranks are populated on the Score Sheet along with Composite scores. The data on this page is populated from the raw scores on the Input page and data on the Trails page (Trails page will eventually be hidden). The problem that I am having is on the Profile page. That page provides Normative Frequencies and is populated from data on the Score Sheet, the Composite Index Sheet, and the Normative Data Sheet. The Composite Index Sheet and the Normative Data Sheet will eventually be hidden as well. The problem that I'm having is the "Normative Frequency of the Difference" column under the Ipsative Comparisons of Trails section. Sometimes it works; sometimes it doesn't. The Normative Frequency of the Difference column is populated by the "T Score Minus Mean" value. However, the table only does positive numbers so I calculate the absolute value on the Normative Data sheet and use the table array there. The formula varies based on the trail number, but Trail 1 is =VLOOKUP('Normative Data!'I46,'Normative Data!'A55:F164,2,FALSE). Trails 2, 3, 4, and 5 are column 3, 4, 5, and 6 respectively.

As stated, sometimes it works and sometimes it doesn't. However, only Trail 1 and 5 work depending on the values. I've tested with multiple "Raw Time in Seconds" combinations and it varies based on that. Any help would be appreciated. I've scoured the forum, YouTube, and Google but nothing has worked.

[Pete_UK]

It took me quite a whiloe to find the formula, but eventually I found it in cell H32 on the Profile sheet. You should really use absolute references to your table, so that it doesn't change when you copy the formula - change it to this instead:

=VLOOKUP('Normative Data'!I46,'Normative Data'!$A$55:$F$164,2,FALSE)

I think your problem relates to trying to lookup on fractional values, and these cannot always be represented with exact accuracy in the binary numbering system that computers use. This is the same problem that occurs with numbers in Base 10, e.g. 1/3 or 2/3 cannot be represented exactly as they are recurring decimals.

Hope this helps.

Pete

[taliesin42421]

Thank you for your prompt reply, and my apologies. It would've been helpful to provide the cell with the formula as opposed to giving vague directions to where I was looking. I saw this suggestion earlier and tried it, but it did not work so I took it out until I could figure out why I was getting the return error. What really confuses me is that it works part of the time. Cell H26 on the same sheet also needs to look up absolute values and decimal values but does it without difficulty and the formula is nearly identical. Thank you again for your reply!

[taliesin42421]

If I do =if(isna(VLOOKUP('Normative Data!'I46,'Normative Data!'A55:F164,2,FALSE)),"No","Yes") it returns No even though the value is available. I'm at a loss.

[thenewkidd]

Vlookup only goes as far as it finds the thing you are looking for.
If you are looking for Thing1 and you have it in several places it will only return the first result and not even consider the other "Thing1"s in the column.

With that knowledge, you have a table Normative Data!'A55:F164 with out the "$" like this 'Normative Data!'$A$55:$F$164, you create a moving target.
Let's say your Thing1 is in Cell A25 (just as an example) it will return a #N/A as the item is outside of the search parameter.

VLOOKUP is fine in some situations but it is limited that the lookup column always has to be the first column in the "Table" and start moving to the right as the column number gets larger.

I suggest moving to an Index and Match that way you can look up anything in any column and match it to another cell from another column and if "isna" it will pull correctly and you don't have to worry if your table is too small.

INDEX('Normative Data!'B:B,MATCH('Normative Data!'I46,'Normative Data!'A:A,0))

This is just an example, the index is the column of the results you want returned and the match is the item you want to look up and the column where you would find that item.

[taliesin42421]

Thank you for your reply. I thought VLOOKUP would work because the lookup value is always in the first column as it applies to all of the conditions. However, it's not finding those first values. I just tried:

=INDEX('NORMATIVE DATA'!F55:F164,MATCH('NORMATIVE DATA!'I50,'NORMATIVE DATA'!A55:A164,0))

The number that it is looking up from 'NORMATIVE DATA!'I50 is in column A. The value that I want returned is in column F. It returned the #N/A value also. I

I've been wondering if it is a formatting error but they are all formatted as Number with one decimal point.

Right now I have pretended that the data is coming from a 52 year old. The raw time in seconds is:

Trail 1=20
Trail 2=30
Trail 3=40
Trail 4=50
Trail 6=60

That will provide results for Trail 1 in cell H32 of the Profile worksheet. However, nothing else works. I did Index/Match for all the cells and it returns a value for Trail 1 but #N/A for everything else. Interestingly, if I flip the scores to Trail 1=60, 2=50, 3=40, 4=30, 5=20, Trail 1 now reads as #N/A and Trail 5 provides a value through both VLOOKUP and INDEX/Match.

[MrShorty]

I'm not sure if this helps, but I cannot replicate your results. In "Normative Data":

=VLOOKUP(I46,$A$55:$F$164,2,FALSE) returns 100 as expected. Copied down four times to capture all 5 values in I46:I50, it returns 100 for each copy, as expected.
=MATCH(I46,$A$55:$A$164,0) returns 110 as expected for all 5 entries in I46:I50.
=ISNA(VLOOKUP(...)) returns FALSE for all 5 entries.

I agree with Pete_UK that this has the feel of floating point errors, but I am unable to find any examples of floating point errors in your spreadsheet. Maybe because there are no realistic inputs and everything seems to be fixed at 0 throughout the calculation?

[taliesin42421]

Thank you for your reply! The only way I get 100 in H32-H36 is if I do not input any values in the C7-C11 on the Input sheet. If I put 20 in C7, 30 in C8, 40 in C9, 50 in C10, and 60 in C11 the error pops up on the Profile sheet. H32=0.4, H33-H36=#N/A. Did you happen to put any values in C7-C11? I would LOVE to see if anybody else is experiencing this. Thank you again for running those formulae!

[MrShorty]

Upon further testing, it does look like floating point error. (More than you want to know about floating point error: https://docs.oracle.com/cd/E19957-01..._goldberg.html ). Here's what I did.

I guessed that inputs would be on the "Input tab" so I started looking to see if I could figure out what inputs belong in the file. I guessed that there should be valid numbers in B7:B11, guessed that numbers between 14 and 19 would fit, and entered =RANDBETWEEN(14,18) into B7:B11 (I get 18, 16, 15, 18, 18). Copy/paste as values to fix the values for testing. Everything still seemed fixed at 0 until I reverse engineered that it needs a valid age in "Score Sheet" cell B6, so I enter 17 into B6 of Score Sheet. That seemed to be enough to get some calculations to propagate throughout the file.

In "Normative data" I have values of 5.6, 0.6, 7.4, 10.6, and 9.4 in I46:I50. I enter a simple subtraction test in J46:J50 to test for floating point error. In J46, I enter =(I46-A136), in J47 =(I47-A161) and so on. Then format each cell as scientific, and I see that each value is not exactly 0 but about +/- 5E-15, which is consistent with floating point errors.

Your lookup table (A55:A164) is sorted in descending order with entries every 0.2. I want to use an approximate match lookup that will always find the right result, so if I subtract 0.01 from the lookup value before doing an approximate match (binary) search. VLOOKUP() can only search on lists sorted in ascending order, so I use MATCH() which can search both ascending and descending (controlled by 3rd argument).

1) subtract small amount from lookup value I46-0.01.
2) Use that for the lookup value in my MATCH() function MATCH(I46-0.01,$A$55:$A$164,-1)
3) For debug purposes, use that inside of and INDEX() function to see if it is finding the correct value =INDEX($A$55:$A$164,MATCH(...)) in K46:K50.

At this point, I can enter different values back in "Input" and compare I46:I50 with K46:K50 until I am satisfied that the lookup reliably and consistently finds the correct record from the lookup table. Once I am convinced that it is working correctly, I can continue with programming the other cells that depend on I46:I50 and the lookup table.

[taliesin42421]

Thanks for the reply! I will give this a shot when I get back to work next week! I'll also need to read up on floating point errors, lol. The input sheet is set up to calculate age and basically populate everything. I appreciate it again!

[taliesin42421]

Thank you again for those suggestions. I tried those and tried changing some things such as changing the table to ascending order from -32.8 to 32.8. I still get the #N/A result. Testing for the floating error, I was only able to find one based on the random numbers (18, 16, 15, 18, 18) that were used and that was only on the second trail. I can see that there must be floating point errors, but I am at a loss for how to bring them about.

[Pepe Le Mokko]

Ididn't read the entire thread but there are some suggestions on how to cope with floating point problems https://learn.microsoft.com/en-US/of...ccurate-result

[taliesin42421]

Thank you!

[Pete_UK]

I think what you need to do is to check if there is an exact match, and if so use your VLOOKUP function, but if there isn't then use an INDEX/MATCH with the third term of the MATCH function set to 1 or -1 depending on whether your data is in ascending or descending order. You can do that using something like:

=IF(COUNTIF(lookup_range,lookup_value)>0, VLOOKUP_formula, INDEX/MATCH_formula)

Hope this helps.

Pete

[taliesin42421]

Thank you all for your input. I couldn't get VLOOKUP, INDEX, or MATCH to work. You were all correct about the floating point errors and they seemed to come from calculating the mean of the five trails and subtracting it from the trail score. The manual for the test uses one decimal period. What I ended up doing was calculating the absolute value because only positive numbers were on the table array. I then used =ROUND(cell,1) to round up or down as needed. That ended up making everything work and I was able to use VLOOKUP for all five trails. I GREATLY appreciate your help!

[MrShorty]

I wonder what you did differently than I did, or what I misunderstood. As near as I can tell, the recommendations I made work very well to resolve the lookup issues related to the floating point errors.

Here's my file