Hello!
I've read the posts about VLOOKUP problems and no one seems to be having this problem, just similar.
I am using a VLOOKUP function in excel 2013 and thought it was all going fantastically until I noted the results it was giving back weren't right.
The problem is is that the function isn't giving back the nearest value from the table that the value is looked up in, it is always rounding down. E.g. if the look up value is 98.19 and the two nearest values from the table are 98.00 and 98.20, then the result will always be 98.00, whereas I need the result to be 98.20 (i.e. the closest, not just rounded down).
The formula I have entered is =VLOOKUP(B16,F15:G29,2,TRUE), which won't help much, but to reiterate that I am using the TRUE value i.e. approximate match (it's just not giving an approximate match). I have to use this because in reality I am using values to 4 decimal places and there will probably never be an exact match (or at least most of the time) so I can't use FALSE.
Fairly confident the formula is functioning correctly; values ascending down and in the first column etc.
Example:
Parameter Value Emulation batch:
d(0.5) 95.417 FA1057
<code>Table 1 </code></pre>
<code>d(0.5) average Batch 92.3510 FA1111 92.4728 FA1114 92.4975 FA1116 92.8868 FA1112 92.8901 FA1113 93.2075 FA1115 93.4474 FA1075 94.6016 FA1067 94.6501 FA1099 94.7286 FA1057 95.4176 FA1053 95.5923 FA1051 95.6800 FA1107 96.3676 FA1033 96.5075 FA1022 </code></pre>
<code></code> </pre>The d(0.5) is the median. I am physically entering the value and this has just been measured on an instrument. In the table are previously measured batches, and by comparing the value of the new sample with these (using the formula), I would use the closest matching batch for emulation.
I have left a value in the value box as an example to demonstrate the problem: the nearest value would give FA1053 but it rounds down to give FA1057
Thanks for your help in advance!
I've read the posts about VLOOKUP problems and no one seems to be having this problem, just similar.
I am using a VLOOKUP function in excel 2013 and thought it was all going fantastically until I noted the results it was giving back weren't right.
The problem is is that the function isn't giving back the nearest value from the table that the value is looked up in, it is always rounding down. E.g. if the look up value is 98.19 and the two nearest values from the table are 98.00 and 98.20, then the result will always be 98.00, whereas I need the result to be 98.20 (i.e. the closest, not just rounded down).
The formula I have entered is =VLOOKUP(B16,F15:G29,2,TRUE), which won't help much, but to reiterate that I am using the TRUE value i.e. approximate match (it's just not giving an approximate match). I have to use this because in reality I am using values to 4 decimal places and there will probably never be an exact match (or at least most of the time) so I can't use FALSE.
Fairly confident the formula is functioning correctly; values ascending down and in the first column etc.
Example:
Parameter Value Emulation batch:
d(0.5) 95.417 FA1057
<code>Table 1 </code></pre>
<code>d(0.5) average Batch 92.3510 FA1111 92.4728 FA1114 92.4975 FA1116 92.8868 FA1112 92.8901 FA1113 93.2075 FA1115 93.4474 FA1075 94.6016 FA1067 94.6501 FA1099 94.7286 FA1057 95.4176 FA1053 95.5923 FA1051 95.6800 FA1107 96.3676 FA1033 96.5075 FA1022 </code></pre>
<code></code> </pre>The d(0.5) is the median. I am physically entering the value and this has just been measured on an instrument. In the table are previously measured batches, and by comparing the value of the new sample with these (using the formula), I would use the closest matching batch for emulation.
I have left a value in the value box as an example to demonstrate the problem: the nearest value would give FA1053 but it rounds down to give FA1057
Thanks for your help in advance!
