VLOOKUP with the TRUE Input: How to Use It

From this tutorial, you will learn how to use the VLOOKUP function with the TRUE input.

VLOOKUP seems to be one of the most utilized functions in Excel nowadays. Its capability to find the data that we want often makes it become a very useful function for many people.

However, it might be sometimes confusing for us to write and use the function properly. One of the problems that often surfaced is when we use VLOOKUP with TRUE as its last input.

In that scenario, we must know exactly what we are doing. Writing the VLOOKUP formula wrong or having the wrong form of data table reference for the VLOOKUP can make us get a wrong VLOOKUP result or even an error. This is surely something that we don’t want for our Excel work results.

Thus, if you also find an error when you use TRUE as your last VLOOKUP input, you have come to the right place. Here, we will discuss why the error or wrong result comes out from the VLOOKUP TRUE and the solution for it.

Curious? Let’s read the next parts of this article.

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!

Brief Description of VLOOKUP

Before we dive more specifically into the VLOOKUP formula with the TRUE input, let’s refresh our minds a bit about VLOOKUP in general.

VLOOKUP is a function in Excel that can be used to look up data in a cell range (usually in the form of a data table) vertically. It finds the data by looking up the reference we input into it in the first column of our cell range and extract the data which has the same row position as that reference in the column of that cell range that we specify.

Here is the general writing syntax of VLOOKUP.



A brief description for each of the VLOOKUP inputs is:

• lookup_value = the reference for our data lookup process that VLOOKUP will try to find in the first column of our cell range reference
• table_array = the cell range reference where we will try to find our data. It is usually in the form of a data table
• col_index_num = the position of the column where we will get our VLOOKUP result. The position is counted from left to right from the far-left column of our cell range reference
• [range_vlookup] = optional input. You can input TRUE or FALSE here. TRUE means VLOOKUP will try to find the smaller nearest value from our lookup reference if it doesn’t find its exact match. FALSE means VLOOKUP will only try to find the exact match of our lookup reference. If we don’t input anything here, VLOOKUP will assume that our input is TRUE

Explanation about VLOOKUP TRUE

As mentioned in the previous part of the tutorial, TRUE is the last (fourth) input that we can give to a VLOOKUP formula. If we don’t input anything for that last input, VLOOKUP will also assume that we input TRUE there.

VLOOKUP with the TRUE input will try to find the smaller nearest value of the lookup reference we input into the VLOOKUP if it doesn’t find its exact match. If you input a text as the lookup reference, then VLOOKUP TRUE will try to find the previous nearest text if we look at the texts in the first column of our cell range reference from A to Z.

If we use VLOOKUP with the TRUE input, then we must make sure that it is okay for VLOOKUP to do that (find the smaller nearest match to our data lookup reference to get our VLOOKUP result). If we aren’t okay with that, we should use FALSE instead as our VLOOKUP last input.

If we decide to use VLOOKUP with the TRUE input, then the first column of our VLOOKUP cell range reference must be in ascending order. This is probably the main cause why many people get an error from their VLOOKUP TRUE as they haven’t sorted that first column yet. If we haven’t sorted it, then our VLOOKUP can produce a wrong answer or even an error.

If we input this TRUE in writing in our previous VLOOKUP writing syntax, then it will become like this.



As previously mentioned, we can also not input anything in the last VLOOKUP input part to make it get TRUE as the last input. If we do that, then our VLOOKUP writing will look like this.



This VLOOKUP writing will produce the same result as our previous writing with the TRUE input.

Implementation Examples

As an implementation example of the VLOOKUP TRUE formula, take a look at the screenshot below.



In the screenshot, we try to find the letter grade for Jenny with her 72 scores. We have the guidance for the letter grade in the table on the left. Since the guidance here only gives a minimum score guide for each letter grade, we use VLOOKUP with the TRUE input to help us. VLOOKUP with the TRUE input is the right solution here since we should find the smaller nearest score in the table to the score we try to find the letter grade of if we cannot find its exact match.

For Jenny, since there is no 72 as the minimum score, VLOOKUP will try to find the smaller nearest value of 72 in the first column of the table, which is 65. This gets Jenny a C letter grade.

This can only be done by VLOOKUP if we input TRUE as its last input. If we input FALSE, we will get an #N/A error as VLOOKUP cannot find the exact match of our reference (72) in the first column of the table and isn’t allowed to find the smaller nearest value of it either.



As mentioned in the previous part, if we don’t input anything in the last VLOOKUP input part, it will assume that we input TRUE there. This means the result if we input TRUE is the same as if we don’t input anything as that last input.



Now, take a look at the first column of the table on the left in the screenshot. You can see there that the minimum scores have been sorted in ascending order. This ensures that our VLOOKUP produces the correct result when we input TRUE (or don’t input anything) as its last input.

What if that first column isn’t in ascending order? Well, something like this can happen.



As you can see there, Jenny gets an F as her letter grade instead of the correct C. That is because our first column in the table isn’t sorted in ascending order. This can cause our VLOOKUP result to go awry.

So, don’t forget to sort the first column of your VLOOKUP cell range reference if you use TRUE as your VLOOKUP last input!

Here is another example of VLOOKUP TRUE if our first column and lookup reference are text values. As you can see, our VLOOKUP will then try to find the previous nearest match to our lookup reference in the order of A to Z.

Things to Remember When You Use VLOOKUP TRUE

To get the result you want from your VLOOKUP formula with TRUE as its last input, here are the things we think you should remember.

• Understand that VLOOKUP TRUE looks for the smaller nearest value of your reference value if it doesn’t find its exact match in the first column. If you want an exact match only, you should use FALSE as your last VLOOKUP input. That way, you shouldn’t be confused when you take a look at your VLOOKUP result


• Always get the first column of the cell range that you use as your second VLOOKUP input sorted in ascending order. That should help reduce significantly the probability that your VLOOKUP gives a wrong result or an error


• Know that if you don’t input anything as your fourth VLOOKUP input, it is the same as if you input TRUE in there

Excel tutorials/articles you might be interested to read: