«

»


How to use last (fourth) parameter of MS Excel VLookUp function efficiently


We have already seen VLookup function with example and precautions need to be taken while using VLookUp function.

VLookup - fourth ( last parameter) example


In our students marks example we have used last parameter of the VLookUp function as 0 (False), that means exact match. Most of the cases we use exact match (0) as last parameter. But in some cases we use True (1) as VLookUp last parameter which is approximate match.

When we use 1 (True) as a last parameter of VLookUp function

If the last parameter of the VLookUp function is 1. It is considered as approximate match. That means if the exact value is not found in the first column of the range, it searches for the next lower value. If next lower value exists it displays the given column value of that range against the lookup value.

For example in our Students example only 5 Student Numbers are exist. For sixth Student Number it displays #N/A, if last parameter is 0. But if we use last parameter as 1 it displays the value against the Student Number 5. Because for Student Number 6 next lower is 5. If we use Student number 1 #N/A will display because next lower value for Student number 1 is not available in the range. Observe the selected part of the above image.

Generally, in most of the cases we use exact match (0 – False). In some cases where operations deals with decimals points we should use (1 – True) as a last parameter of VLookUp function.


Permanent link to this article: http://www.stepsandtips.com/ms-excel/how-to-use-last-fourth-parameter-of-ms-excel-vlookup-function-efficiently/2295/

1 ping

  1. Precautions required to remember while using VLookup | Steps and Tips says:

    [...] How to use last (fourth) parameter of MS Excel VLookUp function efficiently » [...]

Leave a Reply

Your email address will not be published.

”Bloggers