VLOOKUP only rounding down - not giving the correct value

[jimol22]

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!

 

[Andrew Poulsom]

Welcome to MrExcel.

From Excel Help on VLOOKUP:

If an exact match is not found, the next largest value that is less than lookup_value is returned.

If you want the nearest value you will have to adopt a different approach. If you need help with that post some sample data and the expected results.

 

[barry houdini]

Hello jimol22, welcome to MrExcel

That is the way VLOOKUP works, it isn't strictly the "nearest" value. It picks the greatest value smaller than or equal to the lookup value, so effectively rounding down as you say.

For strictly nearest value try this formula

=INDEX(G15:G29,MATCH(MIN(ABS(F15:F29-B16)),ABS(F15:F29-B16),0))

That's an "array formula" which needs to be entered with the key combination

CTRL+SHIFT+ENTER

 

[jimol22]

Ah I see. Does that even apply when using the TRUE parameter though (for approximate match)? Yeah sorry the example data I posted didn't come out very well.

Parameter	Value	Emulation batch:
d(0.5)	95.417	FA1057
	Table 1
	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
		I would expect this to give FA1053, not FA1057 as this is the closest value. What would you suggest?

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[jimol22]

Thank you very much Barry Houdini, that appears to be working. I'll just have to try and figure out how you got there and apply it to my other formulas! Should be easy enough. Jonmo1 I shall also try that

 

[jimol22]

Just a quick one, will it matter if the values are no longer in ascending order? (like they needed to be for the VLOOKUP formula to work)

 

[barry houdini]

No, VLOOKUP with TRUE as 3rd argument requires the lookup range to be sorted ascending but as discussed that won't give you the required results - for the formula I suggested sorting is not required